MySQL - ROLLUP

MySQL - Summarizing Data with ROLLUP

What is the ROLLUP Clause?

The WITH ROLLUP modifier is a powerful extension of the GROUP BY clause in MySQL. It is used to generate summary rows for the groups defined in the GROUP BY list. In essence, it creates subtotals at multiple levels, culminating in a grand total. This is a common requirement in analytical queries and business intelligence reporting.

Think of it like a spreadsheet’s pivot table. You can aggregate data (e.g., sum of sales) first by city, then by state, and finally get a grand total for the entire country. ROLLUP provides this hierarchical summary in a single query.

Syntax

SELECT
    column1, column2, ..., AggregateFunction(columnX)
FROM
    table_name
GROUP BY
    column1, column2, ... WITH ROLLUP;

Example Setup

Let’s create a sales table to track product sales across different regions and years.

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    region VARCHAR(50) NOT NULL,
    year INT NOT NULL,
    product VARCHAR(100) NOT NULL,
    amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO sales (region, year, product, amount) VALUES
('North', 2022, 'Laptop', 120000.00),
('North', 2022, 'Mouse', 5000.00),
('North', 2023, 'Laptop', 150000.00),
('South', 2022, 'Keyboard', 8000.00),
('South', 2023, 'Keyboard', 10000.00),
('South', 2023, 'Mouse', 7000.00);

ROLLUP with a Single Column

The simplest use of ROLLUP is to get a grand total for a single grouping column.

Example 1: Total Sales Amount by Region

SELECT
    region,
    SUM(amount) AS total_sales
FROM sales
GROUP BY region WITH ROLLUP;

The result includes a row for each region’s total sales, plus an extra row where region is NULL, representing the grand total of all sales.

region	total_sales
North	275000.00
South	25000.00
NULL	300000.00

ROLLUP with Multiple Columns (Hierarchical Summaries)

The true power of ROLLUP shines when used with multiple columns. It creates subtotals for each level of the hierarchy specified in the GROUP BY clause, from right to left.

Example 2: Sales Summary by Region and Year

With GROUP BY region, year WITH ROLLUP, MySQL will generate summaries for:

1. Each (region, year) combination.
2. A subtotal for each region (across all its years).
3. A final grand total (across all regions and years).

SELECT
    region,
    year,
    SUM(amount) AS total_sales
FROM sales
GROUP BY region, year WITH ROLLUP;

region	year	total_sales
North	2022	125000.00
North	2023	150000.00
North	NULL	275000.00
South	2022	8000.00
South	2023	17000.00
South	NULL	25000.00
NULL	NULL	300000.00

Distinguishing Subtotals with the GROUPING() Function

The NULL values in the ROLLUP output can be ambiguous. Does NULL mean the data itself is null, or does it represent a subtotal generated by ROLLUP? The GROUPING() function solves this. It returns 1 if the column is aggregated by ROLLUP (i.e., it’s a subtotal row), and 0 otherwise. We can use this with a CASE statement to provide more descriptive labels.

Example 3: Creating a Clean Report

SELECT
    CASE
        WHEN GROUPING(region) = 1 THEN 'All Regions'
        ELSE region
    END AS region_summary,
    CASE
        WHEN GROUPING(year) = 1 AND GROUPING(region) = 0 THEN 'Subtotal'
        WHEN GROUPING(year) = 1 AND GROUPING(region) = 1 THEN 'Grand Total'
        ELSE year
    END AS year_summary,
    SUM(amount) AS total_sales
FROM sales
GROUP BY region, year WITH ROLLUP;

This query produces a much more human-readable report.

Practical Application: Sales Reporting

ROLLUP is invaluable for business analytics. Application backends can use it to generate data for dashboards and reports, showing performance breakdowns by product category, time period, or geographical location, all with a single, efficient database query.

ROLLUP vs. CUBE and GROUPING SETS

- **`WITH ROLLUP`**: Creates hierarchical summaries. For `GROUP BY a, b`, it summarizes `(a, b)`, `(a)`, and `()`. The order matters.
- **`WITH CUBE`**: (Not available in MySQL, but common in other SQL databases like PostgreSQL and SQL Server). Creates summaries for all possible combinations of the grouping columns. For `GROUP BY a, b`, it summarizes `(a, b)`, `(a)`, `(b)`, and `()`.
- **`GROUPING SETS`**: (Not available in MySQL). The most flexible option, allowing you to specify exactly which combinations (grouping sets) you want to aggregate.

For MySQL users, ROLLUP is the primary tool for this type of advanced aggregation. Understanding its hierarchical nature is key to using it effectively.