Advanced Grouping: ROLLUP, CUBE and Pivoting Data
Turn a flat detail query into a report with subtotals, grand totals and columns you can read at a glance
Big idea: A plain
GROUP BY gives you one row per group and nothing more. Real reports also need subtotals, a grand total, and often the data reshaped so that categories run across the top as columns. ROLLUP and CUBE generate the extra super-aggregate rows for you in a single pass, GROUPING() lets you tell those rows apart, and conditional aggregation (SUM(CASE ...)) pivots rows into columns. This lesson wires all four together.
By the end you will be able to:
- add subtotal and grand-total rows with
GROUP BY ... WITH ROLLUP, - use
GROUPING()to distinguish a real NULL from a subtotal marker and to label rows, - reason about multi-dimensional summaries and why column order matters in ROLLUP,
- produce the full cross-tabulation with
CUBE/GROUPING SETSwhere the dialect supports it, and emulate it on MySQL, - pivot rows into columns with conditional aggregation.
1. Our example schema
Everything below runs against one tiny sales table. Create it once and keep it in mind — every result set in this lesson comes from these eight rows.
CREATE TABLE sales (
id INT PRIMARY KEY,
region VARCHAR(20),
product VARCHAR(20),
quarter VARCHAR(2),
amount DECIMAL(10,2)
);
INSERT INTO sales (id, region, product, quarter, amount) VALUES
(1, 'North', 'Widget', 'Q1', 100),
(2, 'North', 'Gadget', 'Q1', 150),
(3, 'North', 'Widget', 'Q2', 120),
(4, 'South', 'Widget', 'Q1', 90),
(5, 'South', 'Gadget', 'Q1', 70),
(6, 'South', 'Gadget', 'Q2', 110),
(7, 'North', 'Gadget', 'Q2', 130),
(8, 'South', 'Widget', 'Q2', 80);The raw rows:
| id | region | product | quarter | amount |
|---|---|---|---|---|
| 1 | North | Widget | Q1 | 100 |
| 2 | North | Gadget | Q1 | 150 |
| 3 | North | Widget | Q2 | 120 |
| 4 | South | Widget | Q1 | 90 |
| 5 | South | Gadget | Q1 | 70 |
| 6 | South | Gadget | Q2 | 110 |
| 7 | North | Gadget | Q2 | 130 |
| 8 | South | Widget | Q2 | 80 |
Total sales are 850. Keep that number handy: it is the grand total every ROLLUP and CUBE below must reproduce.