Welcome to the Treehouse Community
Looking to learn something new?
Samuel Altarac4,961 Points
help with grouping in MySQL
I can't get past the 3rd challenge question which asks:
Like before, select the average "score" as "average", setting to 0 if null, by grouping the "movie_id" from the "reviews" table. Also, do an outer join on the "movies" table with its "id" column and display the movie "title" before the "average". Finally, filter out any "average" score over 2.
I used the following code:
select title, ifnull(avg(score).0) as average from movies left outer join reviews on movies.id = reviews.movie_id group by movie_id having average < 2;
What am I doing wrong?
try: SELECT title, IFNULL(AVG(score), 0) AS average FROM movies LEFT OUTER JOIN reviews ON movies.id = reviews.movie_id GROUP BY movie_id HAVING average < 2;
Kazimierz Matan13,257 Points
I see a dot (.) in "ifnull(avg(score).0)", there should be a comma (,).
Generally, SQL in better readable if you write SQL keywords, functions and other language constructs as all uppercase. You can then easily distinguish them from data (table and column names etc.).
Did this end up working? I still am having issue as well with the following
SELECT title, IFNULL(AVG(score),0) AS average FROM reviews LEFT OUTER JOIN movies ON reviews.movie_id = movies.id GROUP BY movie_id HAVING average < 2;
Yuan Tang14,168 Points
The following worked for me: SELECT movies.title, IFNULL(AVG(score), 0) AS average FROM movies LEFT OUTER JOIN reviews ON movies.id = reviews.movie_id GROUP BY movie_id HAVING average < 2;