Analyzing Trends with GROUP BY

Welcome to the next lesson! In this lesson, we will focus on analyzing Marvel movies trends using the GROUP BY clause. This clause is a crucial part of SQL that allows you to summarize information and gain insights based on specific categories. By the end of this lesson, you will be able to group data effectively and analyze trends using PostgreSQL.

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

The GROUP BY clause groups rows that have the same values into summary rows. It is often used with aggregate functions (COUNT, SUM, AVG, MAX, MIN) to perform calculations on each group of data.

In simpler terms, if you have a dataset with numerous rows, the GROUP BY clause can help you categorize this data into meaningful groups. For example, you might want to group movies by their release year or by the phase they belong to.

Before diving into examples, let's explore the basic syntax of the GROUP BY clause.

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;

• SELECT column1, aggregate_function(column2): Selects the columns you want to include in the result. One column should be a column you want to group by, and the other should be an aggregate function applied to another column.
• FROM table_name: Specifies the table from which to retrieve the data.
• GROUP BY column1: Groups the results by the specified column.

Now, let's write an SQL query to group the movies by their phases and count the number of movies in each phase.

SELECT phase, COUNT(movie_id) AS "Number of Movies"
FROM movies
GROUP BY phase;

• SELECT phase, COUNT(movie_id) AS "Number of Movies": Selects the phase column and counts the number of movie_id in each phase.
• FROM movies: Specifies the table from which to retrieve the data.
• GROUP BY phase: Groups the results by the phase column.

The output is:

 phase | "Number of Movies" 
-------+----------------
     3 |             11
     5 |              3
     4 |              7
     2 |              6
     1 |              6
(5 rows)

The output has two columns. The phase column and Number of Movies column. The output is grouped by the phase value with the corresponding COUNT of movies in each phase.