Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trial
Binu Alexander
4,081 PointsTask 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, include averages under 2.
My code select 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
Problem
I get a error saying something is wrong
3 Answers
Ryan Field
Courses Plus Student 21,242 PointsHi, Binu. You need to add title before IFNULL(AVG(score), 0) as it wants you to display the movie title before the average.
Binu Alexander
4,081 Pointsi tried this : select movies.title, ifnull(avg(score),0) as average from reviews left outer join movies on reviews.movie_id = movies.id group by reviews.movie_id having average < 2
still doesnt work
Ryan Field
Courses Plus Student 21,242 PointsOh, you still want to group from the reviews table, so it should be ...FROM reviews LEFT OUTER JOIN movies ON....
Binu Alexander
4,081 Pointssorry ryan , i'm not sure if i understand your suggestion completely ...
this is what i put it in : SELECT movies.title, IFNULL(avg(score),0) AS average FROM reviews LEFT OUTER JOIN movies ON reviews.movie_id = movies.id GROUP BY reviews.movie_id HAVING average < 2
error : you should try the SELECT statement
Ryan Field
Courses Plus Student 21,242 PointsI see where it's wrong now. We need to do a RIGHT OUTER JOIN instead of a left. This should pass the challenge:
SELECT title, IFNULL(AVG(score), 0) AS average FROM reviews RIGHT OUTER JOIN movies ON reviews.movie_id = movies.id GROUP BY movie_id HAVING average < 2
Binu Alexander
4,081 PointsThanks Ryan , this is working !
SELECT title, IFNULL(AVG(score), 0) AS average FROM reviews RIGHT OUTER JOIN movies ON reviews.movie_id = movies.id GROUP BY movie_id HAVING average < 2
Christopher Loyd
Courses Plus Student 5,806 PointsChristopher Loyd
Courses Plus Student 5,806 PointsIt may be best to describe what it is that you're referring to. Could you post a code snippet, and describe the problem that you're having?