Skip to content
Quantoros

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

Related questions