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

General Discussion

Benjamin Botwin
Benjamin Botwin
13,434 Points
Posted by Benjamin Botwin
Benjamin Botwin
13,434 Points

The last "Grouping, Joining, and Cleaning up" SQL Question.. I just can't get it.

I've been trying to get the last question in this for almost a month and nothing I try works.

I've tried this command on the mysql workbench and it seemed to work but on the website it doesn't.

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 avg(score) <= 2;

Is it broken or am I doing something wrong?

Thanks, Ben

Sreng Hong
Sreng Hong
15,083 Points
Sreng Hong
Sreng Hong
15,083 Points

do you have a link to that question?

2 Answers

Sreng Hong
Sreng Hong
15,083 Points
Sreng Hong
Sreng Hong
15,083 Points

Hey I finally found the answer. Here it is:

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

So I was working on the wrong table... alright.

Thanks a lot!

Cameron Lichtenstein
Cameron Lichtenstein
3,912 Points
Cameron Lichtenstein
Cameron Lichtenstein
3,912 Points

Just to add to this, I was also having a problem with this same question. My original code was:

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 AVG(score) < 2;

I had the movies JOIN reviews instead of reviews JOIN movies as well.

The main problem that I had (and here's where I think this quiz can be improved) is that the error it kept telling me was that I was forgetting the IFNULL() function. Well, obviously I wasn't. So it was frustrating to debug.

Glad I checked the forum here — thanks for your posts guys.