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
Munaro Herbert Moyo
11,063 PointsSQL - Calculating, Aggregating and other functions.
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, include averages under 2. Type in your command below, then press Enter.
SELECT movie.title IFNULL(AVG(score),0) AS average FROM movies LEFT OUTER JOIN reviews ON movies.id = revies.movie_id GROUP BY movie_id WHERE HAVING average < 2;
May you please help, l do not know the problem with my answer
3 Answers
Alexander Sorokin
Courses Plus Student 10,309 PointsSELECT movies.title, IFNULL(AVG(score),0) AS average FROM movies LEFT OUTER JOIN reviews ON movies.id = reviews.movie_id GROUP BY movie_id HAVING average < 2
William J. Terrell
17,403 PointsI don't really get how this one works (it does; I passed the challenge with it, but I don't get how). Would you mid walking me through it?
I tried chopping the statement into bits and piecing the code together that way, but it only made sense once I had this answer as reference.
Like before, select the average "score" as "average", setting to 0 if null,
SELECT IFNULL(AVG(score), 0) AS average
by grouping the "movie_id" from the "reviews" table.
FROM reviews GROUP BY movie_id
Also, do an outer join on the "movies" table with its "id" column (* so join "movies" and "reviews" with "movies" on the LEFT, correct?)
SELECT IFNULL(AVG(score), 0) AS average FROM movies LEFT OUTER JOIN reviews ON movies.id = reviews.movie_id GROUP BY movie_id;
and display the movie "title" before the "average". (*so add "movies.title" between "SELECT" and "IFNULL(AVG...", right?)
SELECT movies.title, IFNULL(AVG(score),0) AS average FROM movies LEFT OUTER JOIN reviews ON movies.id = reviews.movie_id GROUP BY movie_id;
Finally, include averages under 2.
HAVING average < 2;
And get:
SELECT movies.title, IFNULL(AVG(score),0) AS average FROM movies LEFT OUTER JOIN reviews ON movies.id = reviews.movie_id GROUP BY movie_id HAVING average < 2;
Thanks!
A X
12,842 PointsAlexander's answer fails the challenge for me, and I made sure there wasn't an extra space at the beginning.
John Hill
Front End Web Development Techdegree Graduate 35,236 PointsWilliam J. Terrell -- it's putting a lot of commands together, so here's a run-through. let me know if you need further clarification. hopefully, this is helpful. :P
SELECT movies.title,
selects the column called "title" from the table called "movies" via the challenge's request to "display the movie 'title' before the 'average'"
IFNULL(AVG(score),0) AS average
continues the SELECT statement to pull the average score; renames null values to 0 and renames (or Aliases) the AVG(score) column to "average"
FROM movies LEFT OUTER JOIN reviews
since it asks us to join the two tables, our FROM statement gets a bit longer. Think of it like SELECT * FROM [table argument] where the table argument could be a single table or two joined tables.
We use LEFT OUTER JOIN because we put the movies table on the left of the argument and it's that table which has the information we're asked to display (the movie title). If we switched the placement of movies and reviews in the code, we would need to use RIGHT OUTER JOIN. The important point is that since earlier we're asking to select "movies.title", we need to make sure our JOIN statement is pointing towards the table that has that information (here, it's the movies table). If we OUTER JOIN-ed towards the reviews table, we would be unable to access the movie titles because all that's in the reviews table is a movie id and a review score. (someone double check me on this…sounds legit tho!)
ON movies.id = reviews.movie_id
continues the JOIN statement by saying what values to join the two tables on. In this case, we're saying the id column from the movies table (movies.id) and the movie_id column from the reviews table (reviews.movie_id) are to be the point of joining. This is the concept of using integers as Keys so we can link tables together (check out a previous video for more info).
GROUP BY movie_id HAVING average < 2;
finally, the challenge asks us to group by movie_id and include averages under 2. We can use "average" here since we aliased it that way earlier in the code.
Sean Flanagan
33,235 PointsHi John. I know this post is 6 months old but thank you for such a comprehensive explanation for a challenge that really confused me.
John Yzaguirre
22,025 PointsThanks for being so clear and helping beginners learn.
Placid Rodrigues
12,630 PointsThere is a typo. You wrote: revies.movie_id which will be reviews.movie_id
Also, WHERE HAVING is not correct. It should be HAVING ........
Hope that helps. Placid
geoffrey
28,736 Pointsgeoffrey
28,736 PointsMoved to development tools, more suitable.