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!
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
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
Alexander SorokinCourses 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
John HillFront End Web Development Techdegree Graduate 35,236 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
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.
Placid Rodrigues12,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