Introducing Aggregate Functions for Data Analysis

Welcome to this lesson on aggregate functions in PostgreSQL. In the previous lessons, we covered how to use the COUNT function for quantitative analysis and utilized the DISTINCT clause to explore unique values in our data. Now, we will dive into more advanced data analysis techniques. In this lesson, we will explore the SUM, AVG, MAX and MIN functions.

By the end of this lesson, you'll be able to analyze different aspects of the movie data, like calculating total box office earnings, average IMDb ratings, and identifying extreme values.

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

Here is the basic syntax for using aggregate functions.

SELECT aggregate_function(column_name)
FROM table_name;

We want to find the total box office sales for all 33 movies in our dataset. To do this, we use the SUM() aggregate function to add the values of box_office_million_usd of each row in the movie_details table. We also want the resulting table to have a column named "total_sales". The query is:

SELECT SUM(movie_details.box_office_million_usd) AS "Total Sales"
FROM movie_details; 

In this code, we pass the movie_details.box_office_million_usd column into the SUM function. We use AS to create an alias for the column called "Total Sales".

The result is:

 Total Sales
-------------
     30481.9
(1 row)

Using the SUM function, we can see that the total box office sales from all 33 movies is 30481.9 million dollars ($30,481,900,000).