Sql · Aggregate and GROUP BY
Group By
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Introduction to GROUP BY
The
GROUP BY
clause in SQL is used to group rows that have the same values into summary rows. It's typically used with aggregate functions like COUNT(), SUM(), AVG(), etc. to perform calculations on each group of rows.
Let's look at our Codémon shop sales data (there are a lot for this example):
For example, if we wanted to know how many items we've sold in each category, we could use GROUP BY with COUNT():
SELECT category, COUNT(*) AS item_count
FROM sales
GROUP BY category;
This query counts the number of sales records for each unique category. The
GROUP BY category
part tells SQL to group all rows with the same category value together.
GROUP BY vs ORDER BY
Don't confuse GROUP BY with ORDER BY. GROUP BY combines rows with the same values, while ORDER BY just sorts the results. They serve different purposes and can be used together in the same query.
Exercise 2 — GROUP BY with Different Aggregate Functions
GROUP BY
is most powerful when combined with aggregate functions. Let's see how we can use different aggregate functions with grouped data:
SELECT category,
COUNT(*) AS total_sales,
SUM(quantity) AS items_sold,
AVG(price) AS average_price,
MIN(price) AS lowest_price,
MAX(price) AS highest_price
FROM sales
GROUP BY category;
In this query, we're calculating multiple statistics for each category:
-
COUNT(*)
: Number of sales records
-
SUM(quantity)
: Total quantity of items sold
-
AVG(price)
: Average price of items
-
MIN(price)
: Lowest price in the category
-
MAX(price)
: Highest price in the category
Let's try a more specific example - what if we want to see the total value of sales for each product?
SELECT product_name,
SUM(quantity * price) AS total_sales_value
FROM sales
GROUP BY product_name
ORDER BY total_sales_value DESC;
Calculating in Aggregate Functions
You can perform calculations inside aggregate functions. In this example, we multiply quantity by price before summing to get the total sales value.
Exercise 3 — Understanding GROUP BY Results
Consider the following query:
SELECT region, SUM(quantity) AS total_items
FROM sales
GROUP BY region;
What will this query return?
Options
• The total quantity of items sold in each region
• The total number of sales records for each region
• The average quantity of items sold in each region
• A list of all quantities sold, sorted by region
Hint : Look at what the aggregate function SUM() is being applied to, and how the results are being grouped.
Exercise 4 — Grouping by Multiple Columns
You can group by multiple columns to create more specific groups. When you group by multiple columns, SQL creates groups for each unique combination of values in those columns.
For example, let's group sales by both category and region:
SELECT category, region, SUM(quantity) AS total_quantity
FROM sales
GROUP BY category, region
ORDER BY category, region;
This query groups the data first by category, then by region within each category. We get the total quantity sold for each category-region combination.
Let's try another example - getting the total sales value by product and region:
SELECT product_name, region,
SUM(quantity * price) AS total_value
FROM sales
GROUP BY product_name, region
ORDER BY product_name, total_value DESC;
Order of Columns in GROUP BY
The order of columns in the GROUP BY clause determines the hierarchy of grouping. Data is first grouped by the first column, then by the second within each group of the first, and so on.
Exercise 5 — Complete the GROUP BY Query
Complete the SQL query to find the average price of items in each category.
SELECT category, ____(price) AS average_price
FROM sales
____ category;
Options: AVG SUM GROUP BY ORDER BY HAVING WHERE
Hint : Think about which aggregate function calculates an average, and which clause is needed to group the results by category.
Exercise 6 — Match Queries with Their Purpose
Match each SQL query with what it calculates:
Items to match
• SELECT region, COUNT(*) FROM sales GROUP BY region;
• SELECT product_name, SUM(quantity) FROM sales GROUP BY product_name;
• SELECT category, AVG(price) FROM sales GROUP BY category;
• SELECT category, region, MAX(price) FROM sales GROUP BY category, region;
Definitions
• Number of sales records per region
• Total quantity sold for each product
• Average price of items in each category
• Highest price in each category-region combination
Exercise 7 — GROUP BY Column Selection Rules
When using GROUP BY, there's an important rule to remember: Any column that appears in the SELECT clause must either:
1. Be included in the GROUP BY clause, OR
2. Be used with an aggregate function (like COUNT, SUM, AVG, etc.)
This is because GROUP BY combines multiple rows into a single row, so each non-aggregated column must have a single value per group.
Let's see an example of an incorrect query:
-- This will cause an error in most SQL databases
SELECT category, product_name, COUNT(*)
FROM sales
GROUP BY category;
This query will fail because we're selecting
product_name
, but we're only grouping by
category
. Since each category can have multiple products, SQL doesn't know which product_name to show for each group.
Here's the correct way to write this query:
SELECT category, product_name, COUNT(*)
FROM sales
GROUP BY category, product_name;
Common GROUP BY Error
One of the most common errors in SQL is trying to SELECT columns that are neither grouped by nor aggregated. Always check that every column in your SELECT is either in the GROUP BY clause or inside an aggregate function.
Exercise 8 — Selecting Columns with GROUP BY
Which of the following queries will run successfully without errors?
Options
• SELECT category, COUNT(*)
FROM sales
GROUP BY category;
• SELECT category, product_name, COUNT(*)
FROM sales
GROUP BY category;
• SELECT category, price
FROM sales
GROUP BY category;
• SELECT SUM(quantity)
FROM sales
GROUP BY category;
Hint : When using GROUP BY, columns in the SELECT must either be in the GROUP BY clause or used with aggregate functions.
Exercise 9 — Create a Sales Report Query
Complete the SQL query to calculate the total sales value (quantity × price) for each product in each region, sorted by region and then by total value in descending order.
SELECT region, product_name, ____(quantity * price) AS total_value
FROM sales
____ region, product_name
____ region, total_value DESC;
Options: SUM AVG GROUP BY HAVING ORDER BY SORT BY
Hint : Consider which function will give you the total sales value, which clause creates the groups, and which clause arranges the results in the desired order.
Exercise 10 — SQL Execution Order with GROUP BY
In what order does SQL process the following clauses in a query?
Options
• FROM → WHERE → GROUP BY → SELECT → ORDER BY
• SELECT → FROM → WHERE → GROUP BY → ORDER BY
• FROM → GROUP BY → WHERE → SELECT → ORDER BY
• SELECT → FROM → GROUP BY → WHERE → ORDER BY
Hint : Understanding the execution order is important. The database first gets the data (FROM), filters it (WHERE), then groups it (GROUP BY), before selecting columns and applying aggregations.