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
Joe Hilton
Courses Plus Student 4,893 PointsCan anyone point out where I'm going wrong on my MySQL command because I cannot see it anywhere
I'm hoping someone can point out where it is I'm going wrong with this MySQL command because I can't see it myself. The goal is the following:
"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 its "id" column and display the movie "title" before the "average". Finally, filter out any "average" score over 2."
and here is my MySQL command:
SELECT IFNULL(AVG(score)0) AS average FROM reviews LEFT OUTER JOIN reviews ON movies.id = reviews.movie_id GROUP BY movie_id HAVING average > 2
If anyone can point out where the problem is I'd be very grateful
Thanks guys
6 Answers
Alan Johnson
7,625 PointsI see one more issue in your answer. Note that the directions for this step of the code challenge ask you to put the value of a particular column before the average.
André Barro
936 PointsRight Answer( the key was in the RIGHT instead of LEFT):
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;
Alan Johnson
7,625 PointsOoh. I see one more issue in your query. Look at what you're joining to reviews:
FROM reviews LEFT OUTER JOIN reviews ON movies.id = reviews.movie_id
Joe Hilton
Courses Plus Student 4,893 PointsAhh yess. I've changed LEFT OUTER JOIN reviews to movies now. I'm getting there, it's showing the actual table, but it says "Bummer! There's something wrong with your query"
Joe Hilton
Courses Plus Student 4,893 PointsHi again, it's not showing the title column before the average column. This is what I'm trying to fix now
George Abood
3,160 PointsI'm still a little baffled, here's what I thought:
SELECT title, IFNULL(AVG(score),0) AS average FROM reviews LEFT OUTER JOIN movies ON movies.id = reviews.movie_id GROUP BY movie_id HAVING average < 2;
I'm sure I'm missing something.
Alan Johnson
7,625 PointsIt looks like you're missing a comma inside of IFNULL:
SELECT IFNULL(AVG(score), 0) AS average FROM reviews LEFT OUTER JOIN reviews ON movies.id = reviews.movie_id GROUP BY movie_id HAVING average > 2
Joe Hilton
Courses Plus Student 4,893 PointsNope, that doesn't work. I got this error "63386dbc-34c9-4ddf-8794-8cd71eb3cdbf.rb:38:in eval': undefined methodto_a' for "SQL Error: Not unique table/alias: 'reviews'":String (NoMethodError) from 63386dbc-34c9-4ddf-8794-8cd71eb3cdbf.rb:38:in eval' from 63386dbc-34c9-4ddf-8794-8cd71eb3cdbf.rb:38:in"
Jim Withington
12,025 PointsBig spoilers, but it sounds like this was a tough one that maybe has some bugs, so here goes!
What worked for me was this (without the line breaks I included to make it look nice in the forum):
SELECT title, IFNULL(AVG(score), 0) AS average FROM movies
LEFT OUTER JOIN reviews ON reviews.movie_id = movies.id
GROUP BY movie_id HAVING average < 2;
The thing that kept tripping me up was that I kept putting a space like this, which messed up the IFNULL:
IFNULL (AVG(score)
Anyway, it put the challenge in code challenge! Maybe this would be a good one to make as a multistep one, like in some of the HTML/CSS challenges?
Michael Day
10,101 PointsPoor code challenge and hope they fix this soon its been 5 months now.
Andrew Chalkley
Treehouse Guest TeacherHey Michael,
Can you be more specific? What queries are you entering in to make it fail?
Regards
Andrew
Joe Hilton
Courses Plus Student 4,893 PointsJoe Hilton
Courses Plus Student 4,893 PointsIt's displaying the title before the average but it's still saying there's something wrong. Here's my code: "SELECT title, IFNULL(AVG(score), 0) AS average FROM reviews LEFT OUTER JOIN movies ON movies.id = reviews.movie_id GROUP BY movie_id HAVING average > 2"
Alan Johnson
7,625 PointsAlan Johnson
7,625 PointsCheck your conditional in the group by. You want movies with averages that aren't greater than 2.
Joe Hilton
Courses Plus Student 4,893 PointsJoe Hilton
Courses Plus Student 4,893 PointsDo I need to add a WHERE clause? I've tried changing the '>' operator to '<' but nothing shows up. I'm really sorry if I'm beginning to annoy you
Alan Johnson
7,625 PointsAlan Johnson
7,625 PointsNot annoying me at all. Now that you changed the operator to <, what's the full query that you're using for the answer?
Joe Hilton
Courses Plus Student 4,893 PointsJoe Hilton
Courses Plus Student 4,893 PointsSELECT title, IFNULL(AVG(score), 0) AS average FROM reviews LEFT OUTER JOIN movies ON movies.id = reviews.movie_id GROUP BY movie_id HAVING average < 2
Alan Johnson
7,625 PointsAlan Johnson
7,625 PointsSo looking at the answer key (thank goodness I have access to that), the only difference I see is the order of the joins. The answer I have says
movies LEFT OUTER JOIN reviewsrather than vice versa.I just had a chat with Andrew Chalkley about this, and we're going to dig in a little to see if we can make this better very soon, just because I too hit some challenges in completing this.
Joe Hilton
Courses Plus Student 4,893 PointsJoe Hilton
Courses Plus Student 4,893 PointsThanks so much for that Alan! Much appreciated :) I hope you can make it better soon
Alan Johnson
7,625 PointsAlan Johnson
7,625 PointsThank you, Joe! Were you able to complete the code challenge?
Joe Hilton
Courses Plus Student 4,893 PointsJoe Hilton
Courses Plus Student 4,893 PointsYes I was thank god haha! :D