Misuse of aggregate function CS50 Pset 7 10.sql

Question

I'm stuck on pset7 10.sql, it keeps coming up with a misuse of aggregate function. My code is currently:

SELECT name FROM people INNER JOIN directors ON people.id = directors.person_id INNER JOIN ratings ON directors.movie_id = ratings.movie_id WHERE AVG(rating) >= 9.0

As the solution only asks for one column, I thought this would be the right way of doing it?

Thanks in advance

Answer 1

Your AVG is not required. Please also note your catching duplicates (different people with same name). I think what have at the back of your mind is something like this:

SELECT name FROM
(SELECT DISTINCT id, name FROM people INNER JOIN directors ON people.id = directors.person_id INNER JOIN ratings ON directors.movie_id = ratings.movie_id WHERE rating >= 9.0)