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 trialshahardekel
20,306 PointsCode challange trouble (Grouping, Joining and Cleaning Up)
Hi,
I've been trying to submit the 3rd task in the code challange for the "Database Foundations" series on stage 7: "Grouping, Joining and Cleaning".
The challange says: 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.
I tried those two queries, which seem right to me, but it didn't work:
SELECT movies.title, IFNULL(AVG(score).0) AS average FROM reviews LEFT OUTER JOIN movies ON movies.id = reviews.id GROUP BY movie_id HAVING average < 2;
SELECT movies.title, IFNULL(AVG(score).0) AS average FROM reviews LEFT OUTER JOIN movies ON reviews.id = movies.id GROUP BY movie_id HAVING score < 2;
I keep getting an unclear error as follows:
c52b017f-c480-4f62-b78e-048859efb774.rb:38:in `eval': undefined method `to_a' for #<String:0x00000000e5b0f0> (NoMethodError)
from c52b017f-c480-4f62-b78e-048859efb774.rb:38:in `eval'
from c52b017f-c480-4f62-b78e-048859efb774.rb:38:in `<main>'
Googling parts of this error led me to understand that this is related to ruby rather than MySQL, so am I submitting a wrong answer, or is this a bug?
If so, can someone please tell me where am I wrong?
thanks
6 Answers
shahardekel
20,306 Pointsok, so I figured out I had two mistakes in my query:
- The IFNULL function I was using was not used correctly. For some reason I thought it needs a dot to separate between the two parameter, but as usual a comma is needed.
- The review tables has a 'movie_id' and not 'id' as I thought. Had to read the challange again, but finally got this.
The query that worked was:
SELECT movies.title, IFNULL(AVG(score), 0) AS average FROM reviews RIGHT OUTER JOIN movies ON movies.id = reviews.movie_id GROUP BY movie_id HAVING average < 2;
shahardekel
20,306 PointsYiteng, I think you're right. If I remember correctly, I tried using "HAVING average <= 2" but that didn't work. The description of this challenge is very confusing.
Andrew Merrick
20,151 PointsOver 2 does not necessarily mean greater than or equal to 2... I can see where the confusion might be, but if you've done any other programming courses such as PHP, Ruby, or Javascript, if it doesn't say "including X" then it's either greater than or less than.
I hope that makes sense...
Weston Morin
5,507 PointsTHANK YOU! I had gone over this repeatedly and could not find my problems. I ended up to have and "s" missing in "movies.title" and was missing "average" in HAVING average < 2. Made me feel a little better that I was not mile off ;p Thanks again!!
Bryan Hufford
10,052 PointsLike many have echoed, thank you for providing the correct format. It helped me to re-read and spot the silliest error I've made in a bit (adding an extra 's' after the 'e' on 'movie_id'). Once I went through and corrected those, all was well.
Really digging this database course! Hoping that Treehouse adds some more database courses in the future.
Elena Paraschiv
9,938 PointsI initially write it like that. Can someone explain why this is wrong? I didnt get the corect answer. the part of Join of RIGHT OUTER JOIN I didnt understand
SELECT movies.title,
IFNULL(AVG(score), 0) AS average
FROM reviews
RIGHT OUTER JOIN movies
ON movies.id = reviews.movie_id
GROUP BY movie_id
HAVING average < 2;
-IS FROM reviews because AVG column is in the reviews table?'
-why RIGHT OUTER JOIN IF the movies table is on the left and the reviews table on the right? -then shouldnt we keep the same position left right for the ON statement I mean why it is not ON reviews.movie_id = movies.id
shahardekel
20,306 PointsAVG is a function, not a column. AVG(score) means calculate the average score (score is the column in the reviews table, like you said).
Yiteng Zhang
1,889 PointsYiteng Zhang
1,889 PointsThanks, that's really helpful and helped me pass this test. But according to the question " Finally, filter out any "average" score over 2.", shouldn't the answer be "HAVING average <= 2" ?
Dave Faliskie
17,793 PointsDave Faliskie
17,793 Pointstotally thought that was a dot not a , thanks for the post!