Lecture 19: Grouping Data with GROUP BY

This lecture introduces the GROUP BY clause.

So far, our aggregate functions have given us one single result for the entire table (or a filtered subset). For example, SELECT COUNT(*) FROM Sales; tells us the total number of sales. But what if we want to know how many sales we had *for each item*?

This is where the GROUP BY clause comes in. It is one of the most powerful and important concepts in SQL. GROUP BY groups rows that have the same values in specified columns into summary rows. It is almost always used with aggregate functions.

How `GROUP BY` Works

The GROUP BY statement collects all the rows with the same value in a particular column and allows you to perform an aggregate function on each of those groups.

Let’s use our `Sales` table again. We want to count how many of each item we have sold. We want to group the rows by the `ItemName` column and then count the rows in each group.

SELECT
    ItemName,
    COUNT(*)
FROM Sales
GROUP BY ItemName;

Let’s break this down:

• GROUP BY ItemName: This tells the database to create a separate group for each unique value in the `ItemName` column (‘Apple’, ‘Banana’, ‘Orange’).
• SELECT ItemName, COUNT(*): For each of those groups, we select the name of the group (`ItemName`) and then apply the COUNT(*) function to count how many rows are in that group.

The result will be a table that looks like this:

ItemName | COUNT(*)
        -------------------
        Apple    | 2
        Banana   | 2
        Orange   | 1

Using Other Aggregate Functions with `GROUP BY`

You can use any aggregate function with GROUP BY. For example, let’s find the total revenue for each item.

SELECT
    ItemName,
    SUM(Price) AS TotalRevenuePerItem
FROM Sales
GROUP BY ItemName;

This will give us a result like:

ItemName | TotalRevenuePerItem
        -----------------------------
        Apple    | 1.00
        Banana   | 0.60
        Orange   | 0.60

Important Rule for `GROUP BY`

When you use GROUP BY, any column in your SELECT list must either be part of the GROUP BY clause or it must be used inside an aggregate function. You can’t just select a regular column that isn’t being grouped or aggregated.

In the Next Lecture…

We will continue our exploration of data analysis by starting a new mini-project. We will create a database to track student grades and use our new knowledge of aggregate functions and `GROUP BY` to perform some simple analytics, like calculating the average grade for different subjects.

Lecture 18 – Finding Minimum and Maximum Values