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📝 Application Q15 of 15
PostgreSQL - Aggregate Functions and GROUP BY
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?
ASELECT region, product, year, SUM(amount) FROM sales GROUP BY CUBE(region, product, year);
BSELECT region, product, year, SUM(amount) FROM sales GROUP BY region, product, year;
CSELECT region, product, year, SUM(amount) FROM sales GROUP BY GROUPING SETS(region, product, year);
DSELECT region, product, year, SUM(amount) FROM sales GROUP BY ROLLUP(region, product, year);
Step-by-Step Solution
Solution:
Step 1: Understand the requirement
All subtotals and grand totals for region, product, and year means all combinations of these columns.
Step 2: Choose correct grouping method
CUBE generates all combinations, ROLLUP only hierarchical totals, GROUPING SETS needs explicit sets, simple GROUP BY no subtotals.
Final Answer:
SELECT region, product, year, SUM(amount) FROM sales GROUP BY CUBE(region, product, year); -> Option A
Quick Check:
CUBE = all combinations subtotals [OK]
Quick Trick: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
Master "Aggregate Functions and GROUP BY" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
