Nesting Aggregate Functions - SQL

Question

I want to make a SQL query which finds the catagory of awards for movies which has the highest average rating, so for a group of movies which have won a particular award, if they have a higher average rating than any other awards group of movies then it will be returned.

I tried something like this:

SELECT MAX(AVG(m."Rating"))
FROM awards a, movies m
WHERE a."Title" = m."Title"
GROUP BY a."Award"

but it seems that aggregate functions cannot be nested. How can I call the max function on the average ratings for each catagory?

Answer 1

If you are only interested in the value itself, the following should do it:

SELECT MAX(avg_rating)
FROM (
    SELECT AVG(m."Rating") as avg_rating
    FROM awards a, movies m
    WHERE a."Title" = m."Title"
    GROUP BY a."Award"
) t

Otherwise Adrian's solution is better.