Chapter 08 · Aggregation and Grouping
Grouping Sets, Rollups, Cubes, and Portability
Advanced grouping operators generate several aggregation grains in one statement; portability requires understanding both their algebra and their dialect support.
Learning outcomes
Ordinary GROUP BY produces one aggregation grain. GROUPING SETS, ROLLUP, and CUBE request several grains—detail summaries, subtotals, and grand totals—from the same conceptual input.
Explain GROUPING SETS as a set of independent GROUP BY lists.
Derive the levels produced by ROLLUP and CUBE.
Distinguish subtotal placeholder NULLs from NULL values in source data.
Emulate multilevel summaries portably in SQLite with UNION ALL.
GROUPING SETS is the general form
SELECT channel, status, SUM(quantity) AS units_soldFROM saleGROUP BY GROUPING SETS ( (channel, status), (channel), ())ORDER BY channel, status;The first set produces channel-status detail. The second produces channel subtotals. The empty grouping set produces one grand total.
ROLLUP generates hierarchical prefixes
SELECT channel, status, SUM(quantity) AS units_soldFROM saleGROUP BY ROLLUP (channel, status);| ROLLUP level | Equivalent grouping set | Meaning |
|---|---|---|
| Detail | (channel, status) | One row per channel-status combination. |
| Subtotal | (channel) | One row per channel across all statuses. |
| Grand total | () | One row for the entire input. |
Column order matters. ROLLUP(a,b,c) follows a hierarchy from the full key toward progressively shorter prefixes.
CUBE generates every subset
SELECT channel, status, SUM(quantity) AS units_soldFROM saleGROUP BY CUBE (channel, status);For two dimensions, CUBE produces (channel,status), (channel), (status), and (). With n dimensions, a full cube has \(2^n\) grouping sets, so row counts can grow quickly.
Subtotal NULL versus data NULL
A subtotal row often displays NULL in a dimension column because that dimension is not part of the current grouping set. Source data may also contain genuine NULL values. The text alone cannot distinguish them.
SELECT channel, status, GROUPING(channel) AS channel_is_aggregated, GROUPING(status) AS status_is_aggregated, SUM(quantity) AS units_soldFROM saleGROUP BY ROLLUP (channel, status);GROUPING(column) returns a flag indicating that the column is absent from the current grouping set. Use it when labeling subtotal rows.
SQLite portability: explicit UNION ALL
SELECT 'detail' AS level, channel, status, SUM(quantity) AS units_soldFROM saleGROUP BY channel, statusUNION ALLSELECT 'channel subtotal' AS level, channel, NULL AS status, SUM(quantity) AS units_soldFROM saleGROUP BY channelUNION ALLSELECT 'grand total' AS level, NULL AS channel, NULL AS status, SUM(quantity) AS units_soldFROM saleORDER BY level, channel, status;SQLite does not implement GROUPING SETS, ROLLUP, CUBE, or GROUPING(). Explicit branches are verbose but transparent and controllable.
Use level metadata, not NULL alone
WITH summary AS ( SELECT 0 AS level_order, 'detail' AS level_name, channel, status, SUM(quantity) AS units_sold FROM sale GROUP BY channel, status UNION ALL SELECT 1, 'channel subtotal', channel, NULL, SUM(quantity) FROM sale GROUP BY channel UNION ALL SELECT 2, 'grand total', NULL, NULL, SUM(quantity) FROM sale)SELECT level_name, channel, status, units_soldFROM summaryORDER BY level_order, channel, status;The explicit level columns prevent consumers from inferring meaning from NULL placeholders.
Dialect support and syntax
| Platform | Grouping sets / rollup / cube | Practical note |
|---|---|---|
| PostgreSQL | Supports GROUPING SETS, ROLLUP, CUBE, GROUPING | Strong standards-oriented syntax. |
| SQL Server | Supports GROUPING SETS, ROLLUP, CUBE, GROUPING, GROUPING_ID | Use modern GROUP BY syntax rather than legacy WITH ROLLUP/CUBE forms. |
| Oracle Database | Supports GROUPING SETS, ROLLUP, CUBE, GROUPING, GROUPING_ID | Widely used for hierarchical reports. |
| MySQL | Supports GROUP BY ... WITH ROLLUP | Does not provide the full standard GROUPING SETS/CUBE surface in the same form. |
| SQLite | No native grouping extensions | Use UNION ALL or perform subtotal shaping in another layer. |
Performance and design tradeoffs
One conceptual input
Native grouping extensions can share scans and aggregation work.
Combinatorial cube
Every added cube dimension doubles the theoretical grouping-set count.
Different grains
Each subtotal level represents a different row contract.
Consumer clarity
Expose level metadata and stable ordering instead of ambiguous NULL labels.
Practice database
Run this setup once in a disposable SQLite database. Every Chapter 8 example uses the same customer, product, and sale facts so changes in result grain remain easy to compare.
PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS sale;DROP TABLE IF EXISTS product;DROP TABLE IF EXISTS customer;CREATE TABLE customer ( customer_id INTEGER PRIMARY KEY, full_name TEXT NOT NULL, city TEXT, segment TEXT NOT NULL CHECK (segment IN ('consumer', 'business'))) STRICT;CREATE TABLE product ( product_id INTEGER PRIMARY KEY, product_name TEXT NOT NULL, category TEXT NOT NULL, unit_price REAL NOT NULL CHECK (unit_price >= 0)) STRICT;CREATE TABLE sale ( sale_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customer(customer_id), product_id INTEGER NOT NULL REFERENCES product(product_id), quantity INTEGER NOT NULL CHECK (quantity > 0), discount_pct REAL, sold_at TEXT NOT NULL, channel TEXT NOT NULL CHECK (channel IN ('web', 'partner', 'direct')), status TEXT NOT NULL CHECK (status IN ('paid', 'pending', 'refunded')), CHECK (discount_pct IS NULL OR discount_pct BETWEEN 0 AND 100)) STRICT;INSERT INTO customer VALUES (1, 'Nadia Rahimi', 'Tehran', 'consumer'), (2, 'Omar Haddad', 'Berlin', 'business'), (3, 'Lina Chen', NULL, 'consumer'), (4, 'Ava Morgan', 'Berlin', 'consumer'), (5, 'Noah Silva', 'Lisbon', 'business'), (6, 'Marta Costa', NULL, 'business');INSERT INTO product VALUES (10, 'Database Foundations', 'course', 49.00), (11, 'SQL Query Practice', 'course', 69.00), (12, 'SQL Reference Card', 'book', 15.00), (13, 'SQLite Lab Bundle', 'lab', 29.00), (14, 'Data Quality Workbook', 'book', 24.50), (15, 'Database Operations', 'course', 89.00);INSERT INTO sale VALUES (100, 1, 10, 1, NULL, '2026-08-01 09:15:00', 'web', 'paid'), (101, 2, 11, 3, 10, '2026-08-01 10:45:00', 'direct', 'paid'), (102, 1, 12, 2, 5, '2026-08-02 11:30:00', 'web', 'paid'), (103, 4, 10, 1, NULL, '2026-08-03 13:05:00', 'partner', 'pending'), (104, 2, 13, 2, 15, '2026-08-03 15:20:00', 'direct', 'paid'), (105, 5, 15, 1, NULL, '2026-08-04 16:40:00', 'partner', 'paid'), (106, 3, 14, 4, 20, '2026-08-05 08:10:00', 'web', 'refunded'), (107, 5, 12, 5, NULL, '2026-08-05 12:00:00', 'web', 'paid'), (108, 2, 15, 1, 10, '2026-08-06 09:25:00', 'direct', 'pending'), (109, 4, 13, 3, NULL, '2026-08-06 14:50:00', 'partner', 'paid');The data includes repeated cities, customers with no sales, nullable discounts, three sales statuses, and multiple channels. Those variations make aggregate behavior visible.
Chapter capstone
Produce channel-status detail, channel subtotals, status subtotals, and a grand total in SQLite. Include an explicit level name and ordering key.
WITH cube_report AS ( SELECT 0 AS level_order, 'channel + status' AS level_name, channel, status, COUNT(*) AS sale_count, SUM(quantity) AS units_sold FROM sale GROUP BY channel, status UNION ALL SELECT 1, 'channel subtotal', channel, NULL, COUNT(*), SUM(quantity) FROM sale GROUP BY channel UNION ALL SELECT 2, 'status subtotal', NULL, status, COUNT(*), SUM(quantity) FROM sale GROUP BY status UNION ALL SELECT 3, 'grand total', NULL, NULL, COUNT(*), SUM(quantity) FROM sale)SELECT level_name, channel, status, sale_count, units_soldFROM cube_reportORDER BY level_order, channel, status;Checkpoint
Reason about multiple grains
- Which grouping sets are produced by ROLLUP(channel, status)?
- Which additional set does CUBE add for two dimensions?
- Why is a NULL subtotal placeholder ambiguous?
- How can SQLite emulate grouping sets?
- Why can a large CUBE be expensive?
Review the answers
ROLLUP produces (channel,status), (channel), and (). CUBE also adds (status). NULL may be source data or a generated subtotal marker. SQLite can UNION ALL independent grouped queries. A cube contains every subset of dimensions, producing 2^n grouping sets.
Chapter 8 summary
- Aggregate functions summarize rows and usually ignore NULL inputs.
- GROUP BY defines one result row per distinct grouping key.
- WHERE filters input rows; HAVING filters completed groups.
- Conditional aggregation produces several aligned metrics from one grouped input.
- GROUPING SETS, ROLLUP, and CUBE create multiple grains.
- SQLite can emulate multilevel reports explicitly with UNION ALL.
Chapter 9 introduces subqueries, common table expressions, recursion, and set operations—the tools used to compose larger query pipelines.