ROLLUP and CUBE for hierarchical totals in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the ROLLUP operator do in SQL?

ROLLUP creates hierarchical totals by grouping data at multiple levels, starting from detailed rows up to a grand total.

intermediate

How does CUBE differ from ROLLUP in SQL?

CUBE generates all possible combinations of grouping columns, giving totals for every grouping level, not just hierarchical ones.

beginner

Write a simple SQL query using ROLLUP to get sales totals by region and product category.

SELECT region, category, SUM(sales) FROM sales_data GROUP BY ROLLUP(region, category);

intermediate

What is the output difference between GROUP BY ROLLUP(a, b) and GROUP BY CUBE(a, b)?

ROLLUP(a, b) returns totals for (a, b), (a), and grand total. CUBE(a, b) returns totals for (a, b), (a), (b), and grand total.

beginner

Why are ROLLUP and CUBE useful in reports?

They help create summary rows automatically, showing subtotals and grand totals without extra queries.

What does GROUP BY ROLLUP(a, b) produce?

A Totals for (a, b), (a), and grand total

B Totals for (a, b), (b), and grand total

C Totals for (a), (b), and grand total

D Totals for (a, b) only

ROLLUP produces hierarchical totals starting from detailed groups to higher levels: (a, b), then (a), then grand total.

Which operator generates all combinations of grouping columns?

A ROLLUP

B CUBE

C GROUPING SETS

D HAVING

CUBE creates all possible combinations of the grouping columns, including individual and combined totals.

In PostgreSQL, which clause can be used to get subtotals and grand totals in one query?

A ORDER BY

B LIMIT

C WHERE

D GROUP BY ROLLUP

GROUP BY ROLLUP adds subtotal and grand total rows automatically.

If you want totals for each column individually and combined, which should you use?

A ROLLUP

B DISTINCT

C CUBE

D UNION

CUBE returns totals for every combination of columns, including individual and combined.

What keyword helps identify subtotal rows in ROLLUP or CUBE results?

A GROUPING

B HAVING

C ORDER BY

D WHERE

The GROUPING() function returns 1 for columns that are aggregated in subtotal rows.

Explain how ROLLUP works to create hierarchical totals in a sales report.

Think about how totals build up from detailed to summary levels.

Describe the difference between CUBE and ROLLUP and when you might use each.

Consider the number of subtotal rows and grouping combinations.