CODEWITHSUNDEEP
sqloracle-databaseselectaggregate-functions
Sam
Sam

Reputation: 2347

SQL nested aggregate functions

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

Upvotes: 0

Views: 4802

Answers (1)

John Woo
John Woo

Reputation: 263803

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
)

Upvotes: 1

Related Questions