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

shahardekel
shahardekel
20,306 Points
Posted by shahardekel
shahardekel
20,306 Points

Code 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
shahardekel
20,306 Points
shahardekel
shahardekel
20,306 Points

ok, so I figured out I had two mistakes in my query:

  1. 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.
  2. 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;

Yiteng Zhang
Yiteng Zhang
1,889 Points

Thanks, 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
Dave Faliskie
17,793 Points
Dave Faliskie
Dave Faliskie
17,793 Points

totally thought that was a dot not a , thanks for the post!

shahardekel
shahardekel
20,306 Points
shahardekel
shahardekel
20,306 Points

Yiteng, 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
Andrew Merrick
20,151 Points
Andrew Merrick
Andrew Merrick
20,151 Points

Over 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
Weston Morin
5,507 Points
Weston Morin
Weston Morin
5,507 Points

THANK 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
Bryan Hufford
10,052 Points
Bryan Hufford
Bryan Hufford
10,052 Points

Like 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
Elena Paraschiv
9,938 Points
Elena Paraschiv
Elena Paraschiv
9,938 Points

I 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
shahardekel
20,306 Points
shahardekel
shahardekel
20,306 Points

AVG is a function, not a column. AVG(score) means calculate the average score (score is the column in the reviews table, like you said).