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 Anca Dragan
Anca Dragan
8,750 Points

SOLVED!! - Database Foundations SQL Calculating, Aggregating and Other Functions Grouping, Joining and Cleaning Up STEP 3!!

FOR EVERYONE WHO COULDN'T SOLVE THE STEP 3!!

Like before, group by "movie_id" on reviews, select average "score" as "average", filter out averages over 2 and do an outer join on the movies table. Bring back the movie "title" first then the average. Clean up the average and set it to 0 if null.

THE RIGHT ANSWER!!!!

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

John Locke
John Locke
15,479 Points
John Locke
John Locke
15,479 Points

I'm glad you solved this, Dragan. Some of these MySQL lessons aren't as easy as they look. Keep learning.

2 Answers

davpay
davpay
40,173 Points
davpay
davpay
40,173 Points

Thank you for posting this! I've been stuck on it for hours! I had a full stop instead of a comma in the IFNULL statement! Infuriating but happy now it's solved. Would be an idea for Andrew to speak 'full stop' or 'comma' as he's typing for us newbies, I can't tell the difference on a small macbook air.

Thanks again Dragan.

Anca Dragan
Anca Dragan
8,750 Points

I'm glad I could help you, Tom! I've been stuck for an hour or two also, but in the end i could make it right. :) I thought if i write here the solution it would help alot of other programmers that they got stuck at that step.

lyonel scapino
lyonel scapino
14,191 Points
lyonel scapino
lyonel scapino
14,191 Points

this one also worked for me: 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;