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?

user330315 · Accepted Answer · 2011-11-15 20:23:00Z

15

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.

answered Nov 15, 2011 at 20:23

user330315

Sign up to request clarification or add additional context in comments.

Comments

Adriano Carneiro · Accepted Answer · 2011-11-15 18:56:43Z

9

This will bring your desired result:

SELECT a."Award", AVG(m."Rating")
FROM awards a, movies m
WHERE a."Title" = m."Title"
GROUP BY a."Award"
ORDER by AVG(m."Rating") desc
LIMIT 1

This will allow you not only get the MAX value, but its corresponding Award info

answered Nov 15, 2011 at 18:56

Adriano Carneiro

58.8k12 gold badges94 silver badges123 bronze badges

1 Comment

Adriano Carneiro Over a year ago

@Julian I never said LIMIT is SQL standard. It solves OP issue because LIMIT is also available in PostGreSQL (not only MySQL!), which is OP's RDBMS.

Jeff Stock · Accepted Answer · 2011-11-15 18:50:32Z

4

Did you try this?

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

answered Nov 15, 2011 at 18:50

Jeff Stock

3,89612 gold badges49 silver badges63 bronze badges

2 Comments

steve Over a year ago

Thanks, but that has a syntax error "at or near SELECT". Not sure what that would be

Robins Tharakan Over a year ago

@steve, Commenting the inner SELECT SQL with a set of braces would solve that error. The Braces force the executing of the SELECT Scalar query before the MAX() kicks in.

Lukasz Szozda · Accepted Answer · 2019-06-16 10:23:52Z

2

Another way is to use windowed MAX:

SELECT MAX(AVG(m."Rating")) OVER()
FROM awards a -- proper JOIN syntax
JOIN movies m ON a."Title" = m."Title"     
GROUP BY a."Award"
LIMIT 1;

db<>fiddle demo

edited Jun 16, 2019 at 10:23

answered Jun 16, 2019 at 10:13

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Collectives™ on Stack Overflow

Nesting Aggregate Functions - SQL

4 Answers 4

Comments

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related