GROUP BY ROLLUP & CUBE
IntermediateROLLUP and CUBE are extensions to GROUP BY that generate subtotals and grand totals for hierarchical or multi-dimensional data.
Overview
ROLLUP generates subtotals along a hierarchy of columns, while CUBE generates all possible combinations of subtotals. These are useful for reporting and OLAP (Online Analytical Processing) queries. Both are part of SQL:1999 and supported by most modern databases.
ROLLUP: Hierarchical Subtotals
ROLLUP aggregates data along a hierarchy of columns, producing subtotals at each level and a grand total. The order of columns in ROLLUP matters — it defines the hierarchy.
-- Sales subtotals by region, then product
SELECT region, product, SUM(sales) AS total_sales
FROM sales
GROUP BY ROLLUP (region, product);
-- Output:
-- region | product | total_sales
-- -------- | --------- | -----------
-- North | Widget A | 1000
-- North | Widget B | 2000
-- North | NULL | 3000 -- subtotal for North
-- NULL | NULL | 6000 -- grand totalCUBE: Multi-Dimensional Subtotals
CUBE generates all possible combinations of subtotals, making it ideal for multi-dimensional analysis. Unlike ROLLUP, the order of columns does not matter.
-- Sales subtotals across all dimensions
SELECT region, product, SUM(sales) AS total_sales
FROM sales
GROUP BY CUBE (region, product);
-- Output:
-- region | product | total_sales
-- -------- | --------- | -----------
-- North | Widget A | 1000
-- North | Widget B | 2000
-- North | NULL | 3000 -- subtotal for North
-- NULL | Widget A | 1500 -- subtotal for Widget A
-- NULL | Widget B | 4500 -- subtotal for Widget B
-- NULL | NULL | 6000 -- grand totalInterview Questions
Sign in to ask AriaAsk Aria about GROUP BY ROLLUP & CUBE
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.