Applying SQL Aggregate Functions to Multiple Tables

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.