GROUPING SETS
SQL · Grouping Rows
Theory
GROUPING SETS lets one query aggregate at several levels at once. You list each grouping you want in brackets, and the engine returns the union of all of them. GROUP BY GROUPING SETS ((dept), (role), ()) gives a count per department, a count per role and one grand total row, all in a single result.
In rows produced by a grouping that does not include a column, that column shows NULL, meaning "all values". To tell such a subtotal NULL apart from a real NULL in the data, use the GROUPING(col) function, which returns 1 when the column was rolled up. GROUPING SETS is supported by PostgreSQL, SQL Server, Oracle, Snowflake and BigQuery; MySQL and SQLite lack it, so there you write one GROUP BY per level and combine them with UNION ALL.
Why we use it
Reports often need totals at several levels side by side. Without GROUPING SETS you write several GROUP BY queries and UNION ALL them, which is longer and scans the table once per query.
When to use it
Use GROUPING SETS when you need specific, hand-picked aggregation levels. For a strict hierarchy of subtotals use ROLLUP; for every combination use CUBE. On MySQL or SQLite, emulate it with UNION ALL.
How it works
- Read the GROUPING SETS list
- Take the next grouping set, e.g. (dept)
- Group rows by those columns and aggregate
- Fill columns not in this set with NULL
- More grouping sets left? Yes: Process the next set. No: Combine all results.
- Return one combined result
Syntax
SELECT c1, c2, AGG(c3)
FROM table
GROUP BY GROUPING SETS ((c1), (c2), ());Example
SELECT department, role, COUNT(*)
FROM employees
GROUP BY GROUPING SETS ((department), (role), ());Key points
- Avoids running several separate GROUP BY queries and UNIONing them
- The empty set () produces a grand total row
- Columns outside a grouping set show NULL in that set's rows
- GROUPING(col) = 1 marks a subtotal NULL, not a data NULL
- MySQL and SQLite lack it: emulate with UNION ALL