You want to generate a report showing sales totals by year, region, and category, including subtotals for each level and a grand total. Which query correctly uses ROLLUP to achieve this?
hard📝 Application Q8 of 15
PostgreSQL - Aggregate Functions and GROUP BY
You want to generate a report showing sales totals by year, region, and category, including subtotals for each level and a grand total. Which query correctly uses ROLLUP to achieve this?
ASELECT year, region, category, SUM(amount) FROM sales GROUP BY year, region, category WITH ROLLUP;
BSELECT year, region, category, SUM(amount) FROM sales GROUP BY ROLLUP(year, region, category);
CSELECT year, region, category, SUM(amount) FROM sales GROUP BY CUBE(year, region, category);
DSELECT year, region, category, SUM(amount) FROM sales GROUP BY ROLLUP(year), ROLLUP(region), ROLLUP(category);
Step-by-Step Solution
Solution:
Step 1: Identify ROLLUP usage for hierarchical subtotals
ROLLUP(year, region, category) generates subtotals from category up to year and a grand total.
Step 2: Evaluate options
SELECT year, region, category, SUM(amount) FROM sales GROUP BY ROLLUP(year, region, category); correctly uses ROLLUP with all three columns. SELECT year, region, category, SUM(amount) FROM sales GROUP BY CUBE(year, region, category); uses CUBE which generates all combinations, not just hierarchical subtotals. SELECT year, region, category, SUM(amount) FROM sales GROUP BY year, region, category WITH ROLLUP; uses invalid syntax in PostgreSQL. SELECT year, region, category, SUM(amount) FROM sales GROUP BY ROLLUP(year), ROLLUP(region), ROLLUP(category); incorrectly applies multiple ROLLUPs separately.
Final Answer:
SELECT year, region, category, SUM(amount) FROM sales GROUP BY ROLLUP(year, region, category); -> Option B
Quick Check:
ROLLUP with all columns = hierarchical subtotals [OK]
Quick Trick:Use one ROLLUP with all grouping columns for subtotals [OK]
Common Mistakes:
Using CUBE instead of ROLLUP for hierarchical totals
Trying multiple ROLLUP clauses separately
Using MySQL syntax WITH ROLLUP
Master "Aggregate Functions and GROUP BY" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
