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
Wylie Stilwell
8,785 PointsGrouping, Joining, and Cleaning Up Database Foundations Challenge Question 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.
7 Answers
Wylie Stilwell
8,785 PointsAHA! GOT IT! THANK YOU MYSELF FOR ANSWERING MY OWN FORUM POST> HOORAY!
Wylie Stilwell
8,785 PointsYour doing it right. This question is poorly worded and frustrating. The answer is missing only one character:
SELECT movie.title, IFNULL(AVG(score), 0) AS average
FROM reviews RIGHT OUTER JOIN movies
ON reviews.movie_id = movie.id
GROUP BY reviews.movie_id
HAVING IFNULL(AVG(score), 0) <=2;
You can't join on movie.title, you will get null. They have to be the keys that were built together.
And the thing that I forgot, it needs to be <= 2 not < 2.
Bruce Blay
17,650 PointsThanks this helped. It should be plural movieS
SELECT movies.title, IFNULL(AVG(score), 0) AS average FROM reviews RIGHT OUTER JOIN movies ON reviews.movie_id = movies.id GROUP BY reviews.movie_id HAVING IFNULL(AVG(score), 0) <=2;
Charles Kaelin
4,003 PointsHey guys,
Appreciate you'll tackling this question. I had spent almost two hours attempting to solve this specific question. Just wanted to add minor clarification(s) to your answers.
At first I attempted to input the final offered answer and for some reason I was still getting an error message. After putting the code in Workbench, I realized "movie.title" needed to be modified to "movies.title". Similar for the "movie.id" needed to be modified to "movies.id".
So, my final answer that FINALLY worked was as follows: SELECT movies.title, IFNULL(AVG(score), 0) AS average FROM reviews RIGHT OUTER JOIN movies ON reviews.movie_id = movies.id GROUP BY reviews.movie_id HAVING IFNULL(AVG(score), 0) <=2;
Hunter MacDermut
15,865 PointsThank you for this answer. Andrew sure knows how to make everything seem a lot more difficult than it actually is. Though I guess reaching out to the community of coders for help is part of the learning process.
You can replace the extra IFNULL(AVG(score), 0) at the end with the word average, by the way, since it was calculated and aliased earlier in the query.
Wylie Stilwell
8,785 PointsThought maybe i needed to adjust the filter to also display movies with reviews of zed, but that still did not get me the right answer. Ugh this is frustrating.
SELECT IFNULL(AVG(score), 0) as "average"
FROM reviews RIGHT OUTER JOIN movies
ON reviews.movie_id = movies.id
GROUP BY reviews.movie_id
HAVING IFNULL(AVG(score), 0) < 2;
Still no luck. This question does not list any keys to use in the join method, I will try it without the "ON" statement...
Will Lam
7,027 PointsWhat was your answer, Wylie?
Will Lam
7,027 PointsThis is what I came up with:
SELECT movie.title, IFNULL(AVG(score),0) AS average
FROM reviews OUTER JOIN movies
ON reviews.movie_id=movie.title GROUP BY movie_id
HAVING average < 2;
Will Lam
7,027 PointsThanks Wylie!
Wylie Stilwell
8,785 PointsWylie Stilwell
8,785 PointsMy Answer to the code challenge question is:
But the question says I am wrong. I entered the code into workbench and it ran fine, and outputted movie title then average score of every movie with a score below 2. I think perhaps I am interpreting the question wrong, it seems ambiguous to me. Anyone get this one right?