What extra rows does GROUP BY ROLLUP(region, product) add to a normal grouped result?

## The problem ROLLUP solves A report usually wants more than one level of detail at once: sales by region **and** product, a line per region, and one line for the whole company. With plain `GROUP BY` that is three separate queries stitched together with `UNION ALL`, each reading the table again and each padding out the columns it does not group by. `ROLLUP` asks for all those levels in a single statement. ## What ROLLUP expands to `GROUP BY ROLLUP(a, b, c)` is shorthand for a list of **grouping sets** — the prefixes of the column list, longest to empty: ```sql GROUP BY GROUPING SETS ((a, b, c), (a, b), (a), ()) ``` So n columns give n+1 grouping sets. The empty grouping set `()` puts every row into a single group and produces the **grand total** — exactly what an aggregate query with no `GROUP BY` at all returns. The order of the columns matters: `ROLLUP(region, product)` subtotals by region, `ROLLUP(product, region)` subtotals by product. ## Reading the result Rows from the full grouping set are the ordinary detail rows. Every other set produces **super-aggregate** rows — rows summarising several detail rows. In a super-aggregate row, each column the grouping set left out is returned as NULL. Given a `sales(region, product, amount)` table holding `('EU','A',10)`, `('EU','B',20)`, `('US','A',30)`: ```sql SELECT region, product, SUM(amount) FROM sales GROUP BY ROLLUP(region, product); ``` returns six rows: `EU/A/10`, `EU/B/20`, `EU/NULL/30`, `US/A/30`, `US/NULL/30`, `NULL/NULL/60`. Three detail rows, two region subtotals, one grand total. ## Those NULLs are placeholders The NULL in `EU/NULL/30` does not mean the product is unknown; it means "this row is not grouped by product". If the base data also contains genuine NULL products, the two kinds of NULL appear in the same column and cannot be told apart by looking at the value — that is what the `GROUPING()` function exists for. ## Counting the rows The result size is the sum over grouping sets of the number of distinct value combinations in that set: detail groups, plus one row per distinct region, plus one. That growth is worth a thought before rolling up a high-cardinality column such as a customer id. ## Aggregates are recomputed, not summed Every grouping set is evaluated over the underlying rows, not over the rows of the level below. `SUM` and `COUNT(*)` happen to come out the same either way, but `AVG`, `COUNT(DISTINCT …)` and ratios do not — the grand-total `AVG` is the average of all base rows, not the average of the subtotal averages. ## Filtering and ordering `WHERE` still filters base rows before any grouping happens, so it applies to every level. `HAVING` runs after the grouping sets are formed and can filter super-aggregate rows like any other row. And the result has **no defined order** without `ORDER BY`; because placeholder NULLs sort wherever the engine puts NULLs, reporting queries usually sort with `GROUPING()` first so each subtotal lands under its own block. ## Use it for hierarchies ROLLUP fits columns that form a hierarchy — `(country, state, city)`, `(year, month, day)`, `(category, subcategory)` — because the prefixes are exactly the meaningful roll-up levels. Over independent dimensions the prefixes are arbitrary; `CUBE` or an explicit `GROUPING SETS` list is the better fit. You can also pin a column to every level by leaving it outside: `GROUP BY tenant_id, ROLLUP(region, product)` keeps `tenant_id` in all four sets. ## Portability `GROUP BY ROLLUP(...)` is the standard SQL spelling and is what PostgreSQL, Oracle, SQL Server and DB2 accept. MySQL 8 supports only the trailing form `GROUP BY region, product WITH ROLLUP` and has no `CUBE` or `GROUPING SETS`. SQLite supports none of them, so there the `UNION ALL` rewrite is still the answer. Check your engine's documentation before assuming a grouping-set feature is available.