Problems to Solve Welcome Guest | Login or Register

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