calculating a top 5 in php and mysql

Question

Hey I've made a rating system in php and mysql and now I want to select a top 5, I've made something but if a user adds a rating (with the max) it's nr 1 ranking.

How do you guys do it in php and mysql ?

the table looks like this:

-- id

-- mid

-- uid

-- rating

The rating is a number from 1 to 5

Thanks in advance!

Answer 1

SELECT `mid`, SUM(`rating`) AS `total`
FROM `rating`
GROUP BY `mid`
ORDER BY `total` DESC
LIMIT 5

Answer 2

As @chaos points out, this is the idea:

SELECT `mid`, SUM(`rating`) AS `total`
FROM `rating`
GROUP BY `mid`
ORDER BY `total` DESC
LIMIT 5

However, to ensure that not articles with very few ratings get into the top-5 you can add a threshold, allowing only articles with more than X ratings will show up in the result, perhaps giving a more accurate picture of the top 5:

SELECT `mid`, SUM(`rating`) AS `total`, COUNT(1) AS `nrRatings`
FROM `rating`
GROUP BY `mid`
HAVING nrRatings > 5 // This is the threshold. Only articles with more than
                     // 5 ratings will be considered
ORDER BY `total` DESC
LIMIT 5

Answer 3

If I'm understanding your wording, looks like a rating of 1 is the highest? ORDER BY should then be ASC. I think AVG() would be more appropriate than SUM(). Totally agree with the threshold idea, so something like this (shameless c/p rewrite; thx @Machine :P):

SELECT `mid`, AVG(`rating`) AS `avg_rating`, SUM(1) AS `nrRatings`
FROM `rating`
GROUP BY `mid`
HAVING nrRatings > 5 
ORDER BY `avg_rating` ASC
LIMIT 5

Answer 4

Hello,

I was searching for this code months !!!

Thank you very much !

SELECT song_name, AVG(rating) AS avg_rating, SUM(1) AS nrRatings FROM music GROUP BY song_name ORDER BY avg_rating DESC LIMIT 0 , 20