Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

Development Tools

Need help with sql statement.

I'm attempting the code challange at http://teamtreehouse.com/library/grouping-joining-and-cleaning-up and have a problem with the third task. Here's my sql statement so far:

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

could anyone point me in the right direction?

3 Answers

Gareth Borcherds
Gareth Borcherds
9,372 Points

I had to play around with it a bit, but I finally got the following to work. (even though what I was putting in before was the exact same thing)

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

It might have something to do with you <= instead of just <

Thank Gareth, your snippet helped me find what was wrong in mine and get through this task. Here's my final statement:

select movies.title, IFNULL(AVG(reviews.score), 0) as average
from reviews
right outer join movies
on movies.id = reviews.movie_id
group by reviews.movie_id
having average < 2;

Problem solved.

Thank you both. This thread was great in helping me understand where I was going wrong in q3!

Darren.