What is GROUP BY ROLLUP?
SQLLevel: IntermediateAggregations & GROUP BY
What is GROUP BY ROLLUP?
Hints
- Define one output row and the source-table keys.
- Check duplicates, NULLs and the order of filtering and aggregation.
Answer
ROLLUP generates subtotals and a grand total for hierarchical data.
-- Total per region, per department within region, and grand total
SELECT region, department, SUM(sales)
FROM sales_data
GROUP BY ROLLUP(region, department);
NULL in the result represents "all" for that dimension. Use GROUPING() function to distinguish genuine NULLs from rollup NULLs.
Source and licence: SQL Interview Questions — Arda Kocadoru · MIT