Which query correctly uses GROUPING SETS to achieve this?
hard📝 Application Q8 of 15
PostgreSQL - Aggregate Functions and GROUP BY
You want to generate a report showing total sales by city, by product, and by city-product combination from a sales(city, product, amount) table. Which query correctly uses GROUPING SETS to achieve this?
ASELECT city, product, SUM(amount) FROM sales GROUP BY GROUPING SETS ((city), (product), (city, product));
BSELECT city, product, SUM(amount) FROM sales GROUP BY city, product;
CSELECT city, product, SUM(amount) FROM sales GROUP BY ROLLUP(city, product);
DSELECT city, product, SUM(amount) FROM sales GROUP BY CUBE(city, product);
Step-by-Step Solution
Solution:
Step 1: Identify required groupings
We need totals by city, by product, and by city-product pairs separately.
Step 2: Match with GROUPING SETS syntax
SELECT city, product, SUM(amount) FROM sales GROUP BY GROUPING SETS ((city), (product), (city, product)); explicitly lists these three grouping sets correctly.
Final Answer:
SELECT city, product, SUM(amount) FROM sales GROUP BY GROUPING SETS ((city), (product), (city, product)); -> Option A
