Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

Development Tools

SQL - 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

geoffrey
geoffrey
28,724 Points

Moved to development tools, more suitable.

3 Answers

Alexander Sorokin
PLUS
Alexander Sorokin
Courses Plus Student 10,309 Points

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

I 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!

Alexander's answer fails the challenge for me, and I made sure there wasn't an extra space at the beginning.

John Hill
seal-mask
.a{fill-rule:evenodd;}techdegree seal-36
John Hill
Front End Web Development Techdegree Graduate 35,220 Points

William 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
Sean Flanagan
33,232 Points

Hi 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
John Yzaguirre
21,539 Points

Thanks for being so clear and helping beginners learn.

Placid Rodrigues
Placid Rodrigues
12,630 Points

There 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