Nesting Aggregate Functions - SQL

aggregate-functions, postgresql, sql

Solution

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.

Problem

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?

Original source