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

Development Tools

Posted by Darius Strong
Darius Strong
3,956 Points

MYSQL group and cleaning up

The question is "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 it's "id" column and display the movie "title" before the "average". Finally filter out any "average" score over 2."

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 `

it keeps saying bummer!

1 Answer

ivomiranda
ivomiranda
30,286 Points
ivomiranda
ivomiranda
30,286 Points 
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;

Be carefull with your ENTERS and line breaks. I had loads of problems in this database quizzs because the console kind of sucks :P So yes... get used to write your code as I wrote above since it's the best way to read and avoid "bugs" in the quizz console.

James Barnett
James Barnett
39,199 Points
James Barnett
James Barnett
39,199 Points

Ivo Miranda - If you encounter a bug you want fixed, send an email the Treehouse support team at help@teamtreehouse.com so they can take a look at the issue.

ivomiranda
ivomiranda
30,286 Points

James Barnett I wouldn't describe it exactly as a bug that's why I wrote "bugs". It would be an improvement since the console in the database deep dive isn't very user friendly. Example: 1) you can't go back to the previous command to fix the code 2) you are forced to copy paste but even this isn't very easy to do 3) when you copy paste more than 2 lines I had some problems in passing the quizzs and then had to do some arrangements to the code to pass it. But I am not exactly sure of this and don't know if I can replicate it easily

James Barnett
James Barnett
39,199 Points
James Barnett
James Barnett
39,199 Points

Ivo Miranda -

> when you copy paste more than 2 lines I had some problems in passing the quizzs and then had to do some arrangements to the code to pass it

That sounds like an overly strict code correctness check that should be fixed to me :smile: