[Resolved] Need query help. Selecting and averaging.

[sledgeweb]

OK, I have a mysql table with the following fields (for a film review/ranking site):

ratingId: Just an auto_inc unique indentifier

filmId: References a unique ID from another table, isn't unique in this table. Lets me know what film is being rated.

userId: References a number from the user table, lets me know who submitted this ranking.

ratingCat: References a rating category # from another table. This indicates what field the ranking is for (acting, story, cinematography, etc).

rating: The score on a 1-10 scale for the particular category.

OK, now what I want to do, is have a "Top Rated" Box on my site. Let's say the following data is in the table for category #5:

ratingId, filmId, userId, ratingCat, rating
5, 14, 1, 5, 4
10, 14, 2, 5, 9
14, 16, 1, 5, 5
20, 13, 1, 5, 3

Now, there are two votes for filmID #14. A 4 and a 9. So, I need to average this (9+4 = 13 / 2 = 6.5), and return results grouped by the film #, ordered by the average rating. So, I would have returned:

Film 14, Rating 6.5
Film 16, Rating 5
Film 13, Rating 3

How can I get this result set? Of course, this is a simplified example. In a real example, there could be hundreds of ratings for each film.

[sledgeweb]

I did a little playing around, and this seems to work, but some verification that this is a good method would be nice:

SELECT filmId, avg(rating) as rating
FROM Ratings
WHERE ratingCat = 5
group by filmId
ORDER BY rating desc

Returns:

FilmId, Rating
14, 6.5000
16, 5.0000
13, 3.0000

-Sledge

[LordShryku]

That looks right...

[sledgeweb]

Sweet... I threw in a INNER JOIN to get the filmName in there. Seems to be working fine. Thanks for the confirmation!