SQL - Group By Clause

SQL - The GROUP BY Clause

This chapter explains the GROUP BY clause, a fundamental SQL tool for summarizing data. You will learn how to use it with aggregate functions to create powerful summary reports, group by single or multiple columns, and filter groups with the HAVING clause.

Understanding the GROUP BY Clause

The GROUP BY clause is used with a SELECT statement to collect rows that have the same values in one or more columns into summary rows. It is one of the most powerful features in SQL for analytics and reporting.

GROUP BY is almost always used in conjunction with aggregate functions like COUNT(), SUM(), AVG(), MAX(), and MIN(). These functions perform a calculation on a set of rows and return a single summary value for each group.

Syntax

The GROUP BY clause follows the WHERE clause and precedes the HAVING and ORDER BY clauses.

SELECT
    column_to_group_by,
    aggregate_function(column_to_aggregate) AS alias
FROM
    table_name
WHERE
    condition -- Optional: filters rows before grouping
GROUP BY
    column_to_group_by
HAVING
    aggregate_condition; -- Optional: filters groups after grouping

Key Rule: Any non-aggregated column present in the SELECT list must also be present in the GROUP BY clause. Forgetting this is one of the most common SQL errors for beginners.

Practical Examples

Let’s use a sales table to demonstrate. This table records individual product sales.

CREATE TABLE sales (
    order_id INT,
    product_category VARCHAR(50),
    region VARCHAR(50),
    sale_amount DECIMAL(10, 2),
    sale_date DATE
);

INSERT INTO sales VALUES
(1, 'Electronics', 'North', 1200.00, '2023-10-01'),
(2, 'Books', 'South', 55.50, '2023-10-02'),
(3, 'Electronics', 'North', 800.00, '2023-10-03'),
(4, 'Clothing', 'West', 150.00, '2023-10-03'),
(5, 'Books', 'North', 35.00, '2023-10-04'),
(6, 'Electronics', 'South', 2500.00, '2023-10-05');

Grouping by a Single Column

Let’s find the total sales amount for each product category.

SELECT
    product_category,
    SUM(sale_amount) AS total_sales
FROM sales
GROUP BY product_category;

Output

product_category	total_sales
Electronics	4500.00
Books	90.50
Clothing	150.00

Grouping by Multiple Columns

We can get more granular reports by grouping by more than one column. Let’s find the number of sales per category, per region.

SELECT
    product_category,
    region,
    COUNT(order_id) AS number_of_sales
FROM sales
GROUP BY product_category, region
ORDER BY product_category, region;

Output

product_category	region	number_of_sales
Books	North	1
Books	South	1
Clothing	West	1
Electronics	North	2
Electronics	South	1

Filtering with WHERE and HAVING

It’s crucial to understand the difference between WHERE and HAVING:

• WHERE filters individual rows before they are grouped.
• HAVING filters entire groups after the aggregation has been performed.

Example

Let’s find the total sales for each category, but only for the ‘North’ region, and only show categories where the total sales exceeded $100.

SELECT
    product_category,
    SUM(sale_amount) AS total_sales
FROM sales
WHERE region = 'North' -- Step 1: Filter rows BEFORE grouping
GROUP BY product_category
HAVING SUM(sale_amount) > 100; -- Step 2: Filter groups AFTER grouping

Output

In this example, ‘Books’ from the ‘North’ region has sales of $35.00, so it’s filtered out by the HAVING clause.

product_category	total_sales
Electronics	2000.00

Advanced Topic: Window Functions as an Alternative

Modern SQL offers window functions, which can perform aggregate-like calculations without collapsing rows. They are extremely powerful for tasks like calculating running totals or ranking results within a group.

For example, to show each individual sale amount alongside the total sales for its category:

SELECT
    product_category,
    sale_amount,
    SUM(sale_amount) OVER (PARTITION BY product_category) AS category_total_sales
FROM sales;

This query uses a window function to show both the detail (individual sale_amount) and the summary (category_total_sales) in the same row, something impossible with GROUP BY alone.