Find avg of rating for each item.

Question

Hi

I have a table with feilds : file_id, rating, user_id

There is one rating per user_id, so there could be many rating (in scale of 0-5) for a single file_id.

I want to find avg of ratings for every file_id and then display 5 file_id with highest avg rating.

Actually my sql query looks like:

SELECT m.server_domain, m.original_name, m.type, m.title, m.views, 
    m.description, m.hash, AVG(mr.rating_scale5) as avg_rating_scale5 
FROM c7_media m, c7_storage s, c7_media_ratings mr 
WHERE s.public=1 AND m.storage_hash = s.hash AND m.hash = mr.media_hash
GROUP BY mr.media_hash

How should I do this?

Zeeshan

Answer 1

SELECT `file_id`, AVG(`rating`) as a FROM `table` 
GROUP BY `file_id` ORDER BY a DESC LIMIT 5

Replace 'table' with the name of your table.

Answer 2

Group by a file_id and then simply order by the average. Cut off all records that fall below the top 5.

SELECT 
    file_id, AVG(rating) as avg_rating 
FROM 
    table 
GROUP BY 
    file_id 
ORDER BY 
    avg_rating DESC 
LIMIT 5