## 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.

# 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;

PLUS

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.

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.

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.

Thanks for being so clear and helping beginners learn.

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