GROUPING SETS, ROLLUP, and CUBE

## Two different NULLs in one column A grouping-set query can put two kinds of NULL in the same output column: - a **data NULL** — the group really exists, and its grouping key is NULL (rows whose `region` was never filled in collapse into one group whose key is NULL); - a **placeholder NULL** — the row is a super-aggregate produced by a grouping set that excluded that column, so there is no single value to show. They are indistinguishable by value. Any presentation logic that reads the column alone (`COALESCE`, `IFNULL`, a client-side `if (region == null)`) will confuse them, and the symptom is a report with two rows labelled "Total" whose numbers do not match. ## GROUPING() The SQL standard provides `GROUPING(expression)` for exactly this. Its argument must be one of the expressions in the `GROUP BY` clause. It returns: - **1** if that expression is *not* part of the grouping set that produced this row (the value shown is a placeholder); - **0** if it *is* part of the grouping set (the value shown is a real grouping key, NULL or not). ```sql SELECT region, GROUPING(region) AS is_total, SUM(amount) AS total FROM sales GROUP BY ROLLUP(region); ``` For a table with regions `EU`, `US` and some rows with no region at all, this returns `EU/0`, `US/0`, `NULL/0` (the real no-region group) and `NULL/1` (the grand total). The `is_total` column is the only thing separating the last two. ## Labelling The correct label expression tests `GROUPING()` first and only then handles a data NULL: ```sql SELECT CASE WHEN GROUPING(region) = 1 THEN 'All regions' ELSE COALESCE(region, '(no region)') END AS region_label, SUM(amount) AS total FROM sales GROUP BY ROLLUP(region); ``` Writing `COALESCE(region, 'All regions')` instead is the classic bug: it prints "All regions" twice, once for the genuine NULL-region group and once for the grand total. ## Filtering and sorting with it `GROUPING()` is not restricted to the select list. Because it is evaluated per grouped row, it is legal in `HAVING` and in `ORDER BY`: - `HAVING GROUPING(region) = 0` keeps only rows that are grouped by region — a precise way to drop the grand total that, unlike `region IS NOT NULL`, does not also drop the legitimate NULL-region group. - `ORDER BY GROUPING(region), region, GROUPING(product), product` sorts each subtotal after its own detail block, independently of how the engine orders NULLs. That beats relying on `NULLS LAST`, which would also move a data NULL. ## Several columns at once When a query rolls up three or four columns, testing each one separately gets noisy. Engines offer a bit-mask form: PostgreSQL accepts multi-argument `GROUPING(region, product)`, and Oracle and SQL Server offer `GROUPING_ID(region, product)`. Both return an integer whose bits correspond to the arguments, the leftmost argument being the most significant bit: `0` = both columns grouped (a detail row), `1` = only the last argument rolled away, `3` = both rolled away (the grand total). That single integer is convenient as a "level" column for the reporting client to switch on. Which spelling you get is engine-specific, so check the documentation rather than assuming. ## Why the engine cannot just do it for you SQL has no distinct "total marker" value; the standard chose to reuse NULL for the placeholder and to add a function that exposes what the value alone cannot. That design means the *information* is always available, but only if the query asks for it. If your reporting layer needs to know which level a row belongs to, the query must return `GROUPING()` (or the bit mask) as a column — it cannot be reconstructed afterwards from the values. ## Practical rules Select a `GROUPING()` column for every column you roll up when the base data can contain NULLs in those columns; add a `NOT NULL` constraint on grouping keys where the domain allows, which removes the ambiguity at the source; and never let a client infer "this is a total row" from a NULL value alone.