CUBE and ROLLUP
advancedaggregation
2 min read
What are CUBE and ROLLUP?
ROLLUP and CUBE are shorthand for common GROUPING SETS patterns.
- ROLLUP(a, b, c) generates groupings: (a,b,c), (a,b), (a), () — a hierarchy from detailed to grand total.
- CUBE(a, b, c) generates all possible combinations: (a,b,c), (a,b), (a,c), (b,c), (a), (b), (c), ().
Syntax
SELECT col1, col2, SUM(col3)
FROM table_name
GROUP BY ROLLUP (col1, col2);SELECT col1, col2, SUM(col3)
FROM table_name
GROUP BY CUBE (col1, col2);When to Use
- ROLLUP: Hierarchical reports (year → quarter → month)
- CUBE: Cross-tabulation reports needing every combination
- Both: Replacing multiple UNION ALL queries for subtotals
Key Points
- ROLLUP — Produces n+1 grouping sets for n columns, following a left-to-right hierarchy.
- CUBE — Produces 2^n grouping sets for n columns (all combinations).
- Partial — You can mix: GROUP BY col1, ROLLUP(col2, col3).
- GROUPING() — Helps identify which NULLs are from grouping vs. actual data.
- Single Scan — Both CUBE and ROLLUP scan the table once, making them more efficient than multiple queries.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
CUBE and ROLLUP Challenge
Write a query that solve this task: count students by grade with a grand total.
Expected result
One row per grade plus a grand total row where grade is NULL.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What are CUBE and ROLLUP?
Initializing database...Each run starts from fresh sample data.
More Examples
CUBE for all combinations
Aggregate enrollments by both course_id and student_id with all subtotals.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
When should I use ROLLUP vs CUBE?
Use ROLLUP for hierarchical data (e.g., year/month/day). Use CUBE when you need all possible combinations for cross-tabulation.
How many rows does CUBE produce?
For n columns, CUBE produces 2^n grouping sets. For 3 columns, that is 8 different groupings.