Many groupings in one pass
Sometimes you need totals at several levels at once: per region, per product, and a grand total. Rather than running several queries with UNION ALL, GROUPING SETS lets one query compute multiple groupings together.
The three constructs
- GROUPING SETS lists the exact combinations to aggregate, such as region alone, product alone, and the empty set for a grand total.
- ROLLUP generates a hierarchy of subtotals from most detailed to grand total. ROLLUP region, product yields region plus product, then region, then the grand total.
- CUBE generates all combinations of the listed columns, every possible subtotal across the dimensions.
Reading the output
Subtotal rows show NULL in the columns that were rolled up. Because real data can also contain NULL, the GROUPING function distinguishes them: it returns one for a column aggregated away and zero for a genuine value. Wrap it in CASE to print labels like All regions.
When to use which
- ROLLUP fits naturally hierarchical dimensions like year, month, day.
- CUBE suits independent dimensions you want fully cross tabulated.
- GROUPING SETS gives precise control when you want only specific combinations.
Key idea
GROUPING SETS, ROLLUP, and CUBE compute several grouping levels in one query; ROLLUP gives hierarchical subtotals, CUBE gives all combinations, and the GROUPING function tells real nulls from subtotal placeholders.