up vote 0 down vote favorite

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

share|improve this question
100% accept rate
feedback

1 Answer

up vote 1 down vote accepted 
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
)
share|improve this answer
Oh sorry, I think I should rephrase. I meant "Find the movie title of the movie with the lowest average rating." – Sam yesterday
@Sam have you seen the results on sqlfiddle? – Kuya John yesterday
Hmm, yes I see that. For some reason, when I run it against my data I get many results. – Sam yesterday
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.