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

Databases

Posted by Kohane Kagami
Kohane Kagami
12,390 Points

Please help me with this problem.

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.

Here is my code.

select title,avg(score) as average 
ifnull(avg(score),0) 
from reviews 
left outer join movies on review
movie_id=movies.id 
group by movie_id 
having avg(score)<2

2 Answers

Thomas Nilsen
Thomas Nilsen
14,957 Points
Thomas Nilsen
Thomas Nilsen
14,957 Points

Try this one:

SELECT movies.title,  IFNULL(AVG(r.score),0) AS average
FROM reviews r
RIGHT OUTER JOIN movies m ON r.movie_id = m.id
GROUP BY r.movie_id
HAVING IFNULL(AVG(r.score),0) <= 2
Kohane Kagami
Kohane Kagami
12,390 Points

Thanks a lot. It worked.

Paul Penketh
PLUS
Paul Penketh
Courses Plus Student 10,165 Points
Paul Penketh
Paul Penketh
Courses Plus Student 10,165 Points

You're really close. 

SELECT movies.title,  IFNULL(AVG(reviews.score),0) AS average
FROM reviews
LEFT OUTER JOIN movies ON reviews.movie_id = movies.id
GROUP BY reviews.movie_id
HAVING AVG(score) <= 2

You had your join the wrong way, and you were excluding average scores of 2 - when you should only be excluding those higher than 2.

Hope this helps.

Kohane Kagami
Kohane Kagami
12,390 Points

Thanks but something is wrong with your code.