ROLLUP and CUBE for hierarchical totals in PostgreSQL - Step-by-Step Execution

Concept Flow - ROLLUP and CUBE for hierarchical totals

Start with base data

↓

Apply GROUP BY with ROLLUP or CUBE

↓

Generate grouping sets

↓

Calculate aggregates for each grouping set

↓

Return result with hierarchical totals

ROLLUP and CUBE create multiple levels of grouped totals from base data, showing subtotals and grand totals automatically.

Execution Sample

PostgreSQL

SELECT region, product, SUM(sales) AS total_sales
FROM sales_data
GROUP BY ROLLUP(region, product);

This query calculates sales totals by region and product, including subtotals per region and a grand total.

Execution Table

Step	region	product	SUM(sales)	Grouping Level	Action
1	East	Apple	100	region+product	Calculate sum for East & Apple
2	East	Banana	150	region+product	Calculate sum for East & Banana
3	East	—	250	region subtotal	Sum of East region products
4	West	Apple	200	region+product	Calculate sum for West & Apple
5	West	Banana	100	region+product	Calculate sum for West & Banana
6	West	—	300	region subtotal	Sum of West region products
7	—	—	550	grand total	Sum of all sales
8	Exit	No more grouping sets			ROLLUP completed

💡 All grouping sets processed: detailed, subtotals, and grand total

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	After 6	After 7	Final
region	null	East	East	East	West	West	West	null	null
product	null	Apple	Banana	null	Apple	Banana	null	null	null
SUM(sales)	0	100	150	250	200	100	300	550	550

Key Moments - 3 Insights

Why do some rows have NULL in the product column?

Rows with NULL in product represent subtotals for the region (see rows 3 and 6 in execution_table), showing totals for all products in that region.

What does the row with NULL in both region and product mean?

This row (row 7) is the grand total, summing all sales across all regions and products.

How does ROLLUP differ from a simple GROUP BY?

ROLLUP adds extra grouping sets for subtotals and grand totals automatically, unlike simple GROUP BY which only groups by specified columns (see execution_table for grouping levels).

Visual Quiz - 3 Questions

Look at the execution_table, what is the SUM(sales) for the West region subtotal?

A 200

B 300

C 100

D 550

💡 Hint

Check row 6 in the execution_table where region is West and product is NULL.

At which step does the grand total appear in the execution_table?

A Step 1

B Step 3

C Step 7

D Step 5

💡 Hint

Look for the row where both region and product are NULL representing the grand total.

If we replaced ROLLUP with CUBE, how would the number of grouping sets change?

A It would increase

B It would decrease

C It would stay the same

D It would be zero

💡 Hint

CUBE generates all combinations of grouping columns, more than ROLLUP which is hierarchical.

Concept Snapshot

ROLLUP and CUBE add hierarchical totals to GROUP BY queries.
ROLLUP creates subtotals from left to right columns.
CUBE creates all combinations of grouping columns.
NULL in result means subtotal or total level.
Use to get detailed + summary data in one query.

Full Transcript

ROLLUP and CUBE are SQL features that help calculate totals at multiple levels automatically. When you GROUP BY columns with ROLLUP, it adds extra rows for subtotals and a grand total. For example, grouping sales by region and product with ROLLUP shows sales per product, per region subtotal, and overall total. The execution table shows each step: first sums per region and product, then subtotals per region (with NULL product), and finally the grand total (NULL region and product). NULL values in grouping columns indicate these subtotal or total rows. CUBE is similar but creates all possible combinations of grouping columns, resulting in more grouping sets. This helps get detailed and summary data in one query without writing multiple queries.

Practice

1. What does the ROLLUP operator do in a PostgreSQL GROUP BY clause?

easy

A. It filters rows based on a condition before grouping.

B. It creates hierarchical subtotals from left to right for the grouped columns.

C. It generates all possible combinations of grouped columns for totals.

D. It sorts the result set after grouping.

Solution

1. Step 1: Understand ROLLUP purpose

   ROLLUP creates subtotals that roll up from the first column to the last in the GROUP BY list.

2. Step 2: Compare with other options

   CUBE creates all combinations, filtering and sorting are unrelated to ROLLUP.

3. Final Answer:

   It creates hierarchical subtotals from left to right for the grouped columns. -> Option B

4. Quick Check:

   ROLLUP = hierarchical subtotals [OK]

Hint: ROLLUP = subtotals in order of columns [OK]

Common Mistakes:

• Confusing ROLLUP with CUBE
• Thinking ROLLUP filters rows
• Assuming ROLLUP sorts results

2. Which of the following is the correct syntax to use CUBE in a PostgreSQL query?

easy

A. SELECT col1, col2, SUM(sales) FROM table CUBE BY (col1, col2);

B. SELECT col1, col2, SUM(sales) FROM table GROUP BY CUBE col1, col2;

C. SELECT col1, col2, SUM(sales) FROM table GROUP BY ROLLUP(col1, col2);

D. SELECT col1, col2, SUM(sales) FROM table GROUP BY CUBE(col1, col2);

Solution

1. Step 1: Recall correct GROUP BY syntax with CUBE

   The correct syntax is GROUP BY CUBE(col1, col2) with parentheses around columns.

2. Step 2: Identify incorrect options

   SELECT col1, col2, SUM(sales) FROM table CUBE BY (col1, col2); uses invalid 'CUBE BY', C uses ROLLUP, D misses parentheses.

3. Final Answer:

   SELECT col1, col2, SUM(sales) FROM table GROUP BY CUBE(col1, col2); -> Option D

4. Quick Check:

   GROUP BY CUBE(columns) syntax [OK]

Hint: Use GROUP BY CUBE(col1, col2) with parentheses [OK]

Common Mistakes:

• Omitting parentheses in CUBE
• Using 'CUBE BY' instead of 'GROUP BY CUBE'
• Confusing ROLLUP and CUBE syntax

3. Given the table sales with columns region, product, and amount, what will be the output rows count of this query?

SELECT region, product, SUM(amount) FROM sales GROUP BY ROLLUP(region, product);

medium

A. Number of distinct (region, product) pairs plus one subtotal per region plus one grand total row.

B. Number of distinct (region, product) pairs only.

C. Number of distinct regions only.

D. Number of distinct products only.

Solution

1. Step 1: Understand ROLLUP output rows

   ROLLUP(region, product) returns all (region, product) groups, plus subtotals per region, plus a grand total row.

2. Step 2: Count rows

   Total rows = distinct (region, product) + distinct regions (subtotals) + 1 (grand total).

3. Final Answer:

   Number of distinct (region, product) pairs plus one subtotal per region plus one grand total row. -> Option A

4. Quick Check:

   ROLLUP adds subtotals and grand total [OK]

Hint: ROLLUP adds subtotals and grand total rows [OK]

Common Mistakes:

• Ignoring subtotal and grand total rows
• Counting only distinct pairs
• Confusing ROLLUP with CUBE output

4. Identify the error in this query using CUBE:

SELECT region, product, SUM(amount) FROM sales GROUP BY CUBE region, product;

medium

A. GROUP BY cannot use CUBE with more than one column.

B. SUM(amount) cannot be used with CUBE.

C. Missing parentheses around columns in CUBE function.

D. region and product must be in ORDER BY, not GROUP BY.

Solution

1. Step 1: Check CUBE syntax

   CUBE requires parentheses around the columns: CUBE(region, product).

2. Step 2: Validate other parts

   SUM(amount) is valid, multiple columns allowed, ORDER BY is unrelated.

3. Final Answer:

   Missing parentheses around columns in CUBE function. -> Option C

4. Quick Check:

   CUBE syntax needs parentheses [OK]

Hint: Always use parentheses with CUBE columns [OK]

Common Mistakes:

• Omitting parentheses in CUBE
• Thinking SUM is invalid with CUBE
• Confusing GROUP BY and ORDER BY

5. You want to generate a report showing sales totals by region, product, and year, including all subtotals and grand totals. Which query correctly uses CUBE to achieve this?

hard

A. SELECT region, product, year, SUM(amount) FROM sales GROUP BY CUBE(region, product, year);

B. SELECT region, product, year, SUM(amount) FROM sales GROUP BY region, product, year;

C. SELECT region, product, year, SUM(amount) FROM sales GROUP BY GROUPING SETS(region, product, year);

D. SELECT region, product, year, SUM(amount) FROM sales GROUP BY ROLLUP(region, product, year);

Solution

1. Step 1: Understand the requirement

   All subtotals and grand totals for region, product, and year means all combinations of these columns.

2. Step 2: Choose correct grouping method

   CUBE generates all combinations, ROLLUP only hierarchical totals, GROUPING SETS needs explicit sets, simple GROUP BY no subtotals.

3. Final Answer:

   SELECT region, product, year, SUM(amount) FROM sales GROUP BY CUBE(region, product, year); -> Option A

4. Quick Check:

   CUBE = all combinations subtotals [OK]

Hint: Use CUBE for all subtotal combinations [OK]

Common Mistakes:

• Using ROLLUP instead of CUBE for all subtotals
• Confusing GROUPING SETS with CUBE
• Forgetting subtotals with simple GROUP BY