Ordering by mean in MySQL

cichlid

I have a star rating system on my site and would like to show users the top 10 most highly rated items. The table of of star rating data consists of an id (star_rating_id), the item (star_rating_item), the total score (star_rating_score) and the total number of votes (star_rating_votes).

In order to rank the results I wish to calculate the mean score (ie. star_rating_score / star_rating_votes) and then order the results by this mean in descending order.

This is my query:

SELECT star_rating_id,star_rating_score,star_rating_votes,
SUM(star_rating_score/star_rating_votes) AS star_rating_mean 
FROM cms_star_rating_items c
GROUP BY star_rating_id 
ORDER BY star_rating_mean DESC 
LIMIT 0,10

However, this does not appear to be correctly calculating the mean. The first item in my list has a star_rating_score of 159 and star_rating_votes of 37. 159 / 37 should be 4.29, but I get 24.29.

Please could someone offer some pointers on what I'm doing wrong?

Many thanks!

Sxooter

cichlid wrote:
I have a star rating system on my site and would like to show users the top 10 most highly rated items. The table of of star rating data consists of an id (star_rating_id), the item (star_rating_item), the total score (star_rating_score) and the total number of votes (star_rating_votes).

In order to rank the results I wish to calculate the mean score (ie. star_rating_score / star_rating_votes) and then order the results by this mean in descending order.

This is my query:
SELECT star_rating_id,star_rating_score,star_rating_votes,
SUM(star_rating_score/star_rating_votes) AS star_rating_mean 
FROM cms_star_rating_items c
GROUP BY star_rating_id 
ORDER BY star_rating_mean DESC 
LIMIT 0,10
However, this does not appear to be correctly calculating the mean. The first item in my list has a star_rating_score of 159 and star_rating_votes of 37. 159 / 37 should be 4.29, but I get 24.29.

Please could someone offer some pointers on what I'm doing wrong?

Is star_rating_id unique?

Can you give us an example of the data in the table you're working with, specifically all the items with the same star_rating_id that are giving you this problem.