SQL nested aggregate functions

Question

I'm trying to get the lowest average rating for Movies in a database.

Currently I have:

select movie.movietitle as "Lowest Average Rating"
from movie, rating 
where movie.movieid = rating.movieid
group by movie.movietitle
having avg(rating.rating) = min(avg(rating.rating));

But I'm getting the error "group function is nested too deeply." Can anyone provide me with a correct way of doing this and explain why this doesn't work?

Thanks

John Woo · Accepted Answer · 2012-11-20 02:28:42Z

1

SELECT MOVIETITLE AS "Movie Title", AVG(RATING) AS "Lowest Average Rating"
FROM MOVIE, RATING
WHERE MOVIE.MOVIEID = RATING.MOVIEID
GROUP BY MOVIETITLE
HAVING AVG(RATING) =
(
    SELECT MIN(AVG(RATING)) AS "AVGRating"
    FROM MOVIE, RATING
    WHERE MOVIE.MOVIEID = RATING.MOVIEID
    GROUP BY MOVIETITLE
)

answered Nov 20, 2012 at 2:28

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

3 Comments

Sam Over a year ago

Oh sorry, I think I should rephrase. I meant "Find the movie title of the movie with the lowest average rating."

John Woo Over a year ago

@Sam have you seen the results on sqlfiddle?

Sam Over a year ago

Hmm, yes I see that. For some reason, when I run it against my data I get many results.

Collectives™ on Stack Overflow

SQL nested aggregate functions

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related