I have a table
Reviews with columns
In this table, Ratings are associated to a particular MovieID.
For example, MovieID 123 can have 500 ratings, ranging from 1-5.
I want to display N-Top movies, with the highest average rating(rounded to 4 decimals) on the top, in the format:
123 : 4.06
512 : 4.01
744 : 3.68
23 : 2.51
Is this query the right way to do it?
SELECT MovieID, ROUND(AVG(CAST(Rating AS FLOAT)), 4) as avg
from Reviews order by avg desc
Best How To :
It's not the correct way to do it. When you use aggregate function like
avg() you need to include a
group by clause that determines over what item the function should be applied.
In your case you should do
SELECT TOP 5
MovieID, ROUND(AVG(CAST(Rating AS FLOAT)), 4) as avg
GROUP BY MovieID
ORDER BY ROUND(AVG(CAST(Rating AS FLOAT)), 4) DESC
to get the top 5 ratings. The
TOP 5 limits the records returned to the top 5 as determined by the
order by clause.
Note that if the top 6 movies happen to have the same average rating you'd still only get five (and it would be undetermined which five of the six). If you want ties to count as one then you should look at the
rank() function instead.