MySql Avg() doesn’t work with enums or sets

I had a very simple votes table with a rating column which was defined as a SET(‘1′,’2′,’3′,’4′,’5’). This was meant to be easy to rate items from 1 to 5 stars.
Then I had the following values:

item_id rating
1 3
1 4
1 4
1 5

When I ran SELECT AVG(‘rating’) FROM votes WHERE item_id = 1, I would mysteriously get ‘9’ instead of ‘4’.

It turned out that ENUM and SETs return the index of the ratings, in powers of 2 for some reason, instead of the rating values themselves. So SELECT item_id, rating, CONV(rating, UNSIGNED) produced this:
item_id rating CONV(rating, unsigned)
1 3 4
1 4 8
1 4 8
1 5 16

So the average of the indexes of the set would be 9.

Instead I had to convert the set to a string, then convert the string to an unsigned INT to get the results I wanted:
SELECT rating, AVG( CONVERT( CONVERT( rating, CHAR ) , UNSIGNED ) ) FROM votes WHERE item_id = 1;

Trackback this Post | Feed on comments to this Post

Leave a Reply

You must be logged in to post a comment.