Organizing Data with GROUP BY

The Power of Grouping

Categorizing Your Data

In the last lesson, we found the total number of movies. But what if you want to know how many movies exist for each Genre?

The GROUP BY clause allows us to organize data into 'buckets' based on shared values. Once grouped, we can perform math on each bucket individually.

Welcome to Module 4. Previously, we looked at the big picture, but now we'll learn to organize data into specific categories using the GROUP BY clause. Think of it as sorting your movie collection into physical bins by genre or year.

• GROUP BY creates 'buckets' of data based on shared values
• It transforms detailed rows into summary rows
• Essential for category-level analysis

From Totals to Categories

Why Group Data?

Previously, you used COUNT(*) to find the total number of movies. But what if you want to see the count for each Genre? The GROUP BY clause collapses rows with identical values into summary rows, allowing you to perform calculations per category.

Imagine you have a long list of movies. Instead of just counting the total, you want to see how many belong to each genre. The GROUP BY clause acts like a sorter, putting every 'Sci-Fi' movie in one bucket and every 'Drama' in another.

• GROUP BY organizes rows into summary groups.
• It is used with aggregate functions like COUNT().
• It transforms detailed data into categorical insights.

The Golden Rule of Grouping

The Golden Rule

When using GROUP BY, every column in your SELECT statement must either be:

• Part of an aggregate function (like COUNT)
• Listed in the GROUP BY clause

Look at this query. We are selecting Genre and counting the rows. Because we are grouping by Genre, the database knows exactly which Genre to show for each row. But if we tried to select 'Title' without grouping it, the database would get confused—which specific title should it show for the 'Action' group? This results in an error.

• Non-grouped columns cause errors.
• The database needs to know which value to display for a summary row.

Visualizing the Schema

Our Database Structure

To write effective queries, you must understand how the Movie and Rating tables connect.

Notice the RatingCode in the Movie table—this is a Foreign Key that links to the Rating table's Primary Key.

Before we write the code, let's look at our schema. The Movie table holds our main data, including the RatingCode. This code acts as a bridge to the Rating table, where the full descriptions of age ratings are stored.

• Movie table contains Genre, Year, and RatingCode
• RatingCode links Movie to the Rating table
• Understanding keys is vital for grouping related data

Syntax: Movies per Genre

Grouping by Genre

To find the count of movies for each genre, we use the following syntax:

SELECT Genre, COUNT(*)
FROM Movie
GROUP BY Genre;

Let's break down the syntax. First, we select the column we want to categorize by. Then, we add our aggregate function. Finally, we tell the database to organize the results by that same column. When executed, the database returns a clean summary showing each unique Genre and its corresponding count.

• The column in SELECT usually matches the column in GROUP BY.
• COUNT(*) counts all rows within that specific group.

SQL Syntax: GROUP BY

The Basic Structure

The GROUP BY clause always follows the FROM clause (and the WHERE clause, if you have one).

SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name;

Here is the standard syntax. You select the column you want to group by, followed by an aggregate function like COUNT. Crucially, the GROUP BY clause must come after your FROM statement to tell the database exactly how to slice the data.

• Keywords like SELECT and GROUP BY should be UPPERCASE
• The grouped column must appear in the SELECT list
• COUNT(*) calculates the total for each specific group

Example: Movies per Genre

Analysis in Action

Let's find out which Genre is most common. By grouping by Genre, SQL creates one row for each unique category.

SELECT Genre, COUNT(*)
FROM Movie
GROUP BY Genre;

Let's run a query to count movies per genre. Notice how the result doesn't show every individual movie anymore. Instead, it shows a summarized list where each genre has a single total value.

• Each unique Genre appears only once in the result
• The count reflects the number of rows in that specific bucket

Grouping by Year

Analyzing Production by Year

You can group by any column. Let's see which Year had the most movie releases. Complete the query below.

It is your turn to try the logic. We want to see the number of movies released per Year. Fill in the missing parts of the query to group our data correctly. Excellent! By grouping by Year, you've collapsed the individual movie dates into a yearly summary.

• Grouping works for numeric columns like Year.
• The logic remains identical to grouping by text.

The Golden Rule of Grouping

Avoiding Errors

When using GROUP BY, every column in your SELECT statement must be either:

• Part of the GROUP BY clause.
• Inside an aggregate function (like COUNT, SUM, or AVG).

There is one 'Golden Rule' you must follow to avoid errors. If you try to select a column like 'Title' without grouping by it, the database gets confused. Every column must either be a category bucket or a calculated total.

• Non-aggregated columns outside of GROUP BY cause errors
• The database needs to know which value to display for the group

Interactive Exercise: Rating Counts

Final Challenge

Write a query to find the number of movies associated with each RatingCode in the Movie table.

Your results should show two columns: RatingCode and the total count of movies.

Use the Movie table schema to write your query. Remember the Golden Rule: whatever you select must be grouped or aggregated.

• Apply GROUP BY to the RatingCode column.
• Use COUNT(*) for the calculation.

Interactive Exercise: Counting Ratings

Your Turn!

Imagine you want to see how many movies are assigned to each RatingCode (like G, PG, or R).

Task: Write a query to find the number of movies for each RatingCode in the Movie table.

It's time for you to practice. Write a query that groups the Movie table by RatingCode and counts the results. Don't forget to use UPPERCASE for your SQL keywords!

• Apply GROUP BY to the RatingCode column
• Use COUNT(*) to find the totals