Welcome back! In this lesson, we're going to dive into SQL aggregate functions on multiple tables. This lesson will combine everything you have learned so far about SQL queries.

Let's get started putting everything you've learned together.

For convenience, the tables and columns in our Marvel movies dataset are:

Movies Table

 movie_id | movie_name | release_date | phase 
----------+------------+--------------+-------

Movie Details Table

 movie_id | budget_million_usd | box_office_million_usd | imdb_rating | runtime_minutes 
----------+--------------------+------------------------+-------------+-----------------

Characters Table

 character_id | movie_id | character_name | actor | screen_time_minutes 
--------------+----------+----------------+-------+---------------------

Let's begin with our first example, where we will aggregate total box office earnings by phase. We will use an INNER JOIN on the movies and movie_details tables. We will then use the SUM function and the GROUP BY clause to find the total box office earnings per phase. This advanced query is:

SELECT movies.phase, 
SUM(movie_details.box_office_million_usd) AS "Total Box Office"
FROM movies
INNER JOIN movie_details ON movies.movie_id = movie_details.movie_id
GROUP BY movies.phase;

There's a lot going on in this query. Let's break it down step by step.

• We first use SELECT to obtain the movies.phase column and the sum of box office sales. We use the alias Total Box Office for the result of the SUM.
• FROM movies selects the primary table
• INNER JOIN movie_details specifies the table to join with movies
• ON movies.movie_id = movie_details.movie_id matches the rows of the movies and movie_details column based on the movie_id
• GROUP BY movies.phase specifies the rows of the output table each correspond to a phase

The output is:

 phase | Total Box Office 
-------+------------------
     3 |          13501.8
     5 |             1521
     4 |           6374.8
     2 |           5272.3
     1 |           3812.0
(5 rows)

The output shows the sum of box office sales for each phase.

In this example, we want to find the average IMDb score for the movies in each phase that feature the character Thor. The steps are:

• Use INNER JOIN to join all 3 tables based on the movie_id column.
• Find the average IMDb rating
• Filter the movies by only using movies that feature Thor.
• Group the rows by movies.phase
• Sort the average IMDb ratings in descending order.

Let's take a look at the most advanced query yet:

SELECT movies.phase, AVG(movie_details.imdb_rating) AS "Average Rating"
FROM movies
INNER JOIN movie_details ON movies.movie_id = movie_details.movie_id
INNER JOIN characters ON movies.movie_id = characters.movie_id
WHERE characters.character_name LIKE '%Thor%'
GROUP BY movies.phase
ORDER BY "Average Rating" DESC;

Let's look at each line individually:

SELECT movies.phase, AVG(movie_details.imdb_rating) AS "Average Rating"

• This line specifies that our result should have a phase column from the movies table and a column called Average Rating with the average IMDb rating

FROM movies

• Specifies the movies table as the primary table from which data will be fetched.

INNER JOIN movie_details ON movies.movie_id = movie_details.movie_id

• Combines the movies table with the movie_details table, matching rows based on the movie_id column, which must be present in both tables.

INNER JOIN characters ON movies.movie_id = characters.movie_id

• Further combines the result set with the characters table, again matching rows based on the movie_id column.

WHERE characters.character_name LIKE '%Thor%'

• Filters the records to include only those where the character_name column in the characters table contains the text "Thor". The % wildcard allows for any characters to appear before or after "Thor".

GROUP BY movies.phase

• Groups the resulting records by the phase column from the movies table, ensuring that the average IMDb rating is calculated separately for each phase.

ORDER BY "Average Rating" DESC;

• Sorts the final result set in descending order based on the calculated Average Rating.

The output is:

 phase |   Average Rating   
-------+--------------------
     3 | 8.2333333333333333
     1 | 7.5000000000000000
     4 | 7.5000000000000000
     2 | 7.1000000000000000
(4 rows)

Let's analze this output:

• phase: This column represents the phase number from the movies table.
• Average Rating: This column shows the calculated average IMDb rating for movies featuring Thor.

Phase 3

• The average IMDb rating for movies featuring Thor in Phase 3 is approximately 8.23.

Phase 1

• The average IMDb rating for Thor movies in Phase 1 is 7.50.

Phase 4

• The average IMDb rating for Thor movies in Phase 4 is also 7.50.

Phase 2

• The average IMDb rating for Thor movies in Phase 2 is approximately 7.10.

This allows us to see at a glance which phase had the highest and lowest average IMDb ratings for movies featuring Thor.

Congratulations on finishing the last lesson of this course. In this lesson, we covered how to use SQL aggregate functions such as SUM() and AVG() alongside the GROUP BY clause in PostgreSQL. We also demonstrated how to join multiple tables and filter data using the WHERE clause with conditions like LIKE.

Now it's your turn to practice these concepts. The upcoming exercises will help you solidify your understanding and build confidence in writing SQL queries using aggregate functions.

Great work, and happy querying!