How would you return per-region, per-product and overall totals in one query using GROUPING SETS?

## The construct `GROUPING SETS` takes an explicit list of grouping-key lists and produces the union of the results of grouping by each of them: ```sql SELECT region, product, SUM(amount) AS total FROM sales WHERE order_date >= DATE '2026-01-01' GROUP BY GROUPING SETS ((region), (product), ()); ``` Three sets, three levels in one result: one row per region, one row per product, and one grand-total row. Each inner parenthesised list is one level; `()` — the empty grouping set — means "no grouping keys at all", which puts every qualifying row into a single group, exactly as an aggregate query with no `GROUP BY` would. ## The shared select list and its NULL padding All levels share one select list, so every level must supply a value for every output column. A level that does not group by `product` returns NULL there. That is not an accident of implementation; it is the same padding you would write manually in the UNION version: ```sql SELECT region, CAST(NULL AS varchar) AS product, SUM(amount) FROM sales GROUP BY region UNION ALL SELECT NULL, product, SUM(amount) FROM sales GROUP BY product UNION ALL SELECT NULL, NULL, SUM(amount) FROM sales; ``` Seeing the two side by side makes the trade obvious. The UNION version repeats the table reference and the `WHERE` clause three times — three chances for them to drift when someone adds a filter — and reads the input once per branch. The GROUPING SETS version states the filter once and lets the engine compute all levels together. ## Multiple columns inside one set Inner lists may hold several columns: `GROUPING SETS ((region, product), (region), ())` groups by the pair, then by region alone, then overall — which is precisely `ROLLUP(region, product)`. That is the relationship between the three constructs: `GROUPING SETS` is the general form, `ROLLUP` and `CUBE` are abbreviations for the prefix list and the power set respectively. When you want an arbitrary handful of levels — say `(region, product)`, `(product)` and `()` but not `(region)` — only the explicit list can express it. ## Duplicates and the empty set A grouping set list is taken literally. Listing the same set twice produces its rows twice; nothing deduplicates them. Omitting `()` means no grand total at all — a frequent oversight, because `ROLLUP` and `CUBE` always include it and people assume `GROUPING SETS` does too. `GROUPING SETS (())` on its own is legal and returns exactly one row, the overall aggregate. ## Telling the levels apart Because the levels share a shape, the consumer needs to know which level each row came from. Add the flags: ```sql SELECT region, product, GROUPING(region) AS no_region, GROUPING(product) AS no_product, SUM(amount) AS total FROM sales GROUP BY GROUPING SETS ((region), (product), ()); ``` Now `no_region = 1, no_product = 0` marks a per-product row unambiguously, even if some sales rows genuinely have a NULL region. Sorting matters too: results have no defined order, and a report usually wants `ORDER BY GROUPING(region), region, GROUPING(product), product` or an explicit level column so the totals land where a reader expects them. ## Where each clause applies `WHERE` is evaluated before grouping and so applies identically to every level — one of the main benefits over the UNION rewrite. `HAVING` is evaluated per grouped row and can therefore filter individual levels, for example `HAVING GROUPING(region) = 1 OR SUM(amount) > 1000`. Aggregate values are always computed from the base rows for that level, never from the rows of another level. ## Portability `GROUPING SETS` is standard SQL and is supported by PostgreSQL, Oracle, SQL Server and DB2. MySQL 8 does not offer it (only `WITH ROLLUP`), and SQLite does not either, so on those engines the `UNION ALL` rewrite remains the portable formulation — worth remembering before a query written for one engine is promised to another. Check your engine's documentation rather than assuming.