You want to analyze sales data by region and product and also see subtotals for each region and product separately, plus a grand total. Which query should you use?
hard📝 Application Q9 of 15
PostgreSQL - Aggregate Functions and GROUP BY
You want to analyze sales data by region and product and also see subtotals for each region and product separately, plus a grand total. Which query should you use?
ASELECT region, product, SUM(amount) FROM sales GROUP BY CUBE(region, product);
BSELECT region, product, SUM(amount) FROM sales GROUP BY ROLLUP(region, product);
CSELECT region, product, SUM(amount) FROM sales GROUP BY GROUPING SETS ((region), (product));
DSELECT region, product, SUM(amount) FROM sales GROUP BY region, product;
Step-by-Step Solution
Solution:
Step 1: Understand the requirement
We need subtotals for region, product, their combinations, and a grand total.
Step 2: Match with query options
CUBE(region, product) generates all combinations including individual region, product, combined, and grand total subtotals. ROLLUP does hierarchical subtotals only. GROUPING SETS in SELECT region, product, SUM(amount) FROM sales GROUP BY GROUPING SETS ((region), (product)); misses combined grouping. SELECT region, product, SUM(amount) FROM sales GROUP BY region, product; has no subtotals.
Final Answer:
SELECT region, product, SUM(amount) FROM sales GROUP BY CUBE(region, product); -> Option A
Quick Check:
CUBE = all subtotals including separate and combined [OK]
Quick Trick:Use CUBE for all combinations subtotals [OK]
Common Mistakes:
Using ROLLUP when all combinations are needed
Missing combined grouping sets
Not including grand total
Master "Aggregate Functions and GROUP BY" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
