Aggregating Data at Multiple Levels with GROUPING SETS
Use GROUPING SETS for Multi-Level Aggregation
Write a SQL query to calculate total sales at multiple levels (e.g., by year, region, and overall).
Solution:
-- Calculate total sales at multiple levels using GROUPING SETS.
SELECT
Year,
Region,
SUM(SalesAmount) AS TotalSales
FROM SalesData
GROUP BY GROUPING SETS (
(Year, Region), -- Group by year and region.
(Year), -- Group by year only.
() -- Overall total (no grouping).
);
Explanation:
- The goal is to demonstrate how to use GROUPING SETS to perform multi-level aggregation in a single query.
- GROUPING SETS : Allows multiple grouping combinations in one query.
- (Year, Region) : Groups by both year and region.
- (Year) : Groups by year only.
- () : Represents the overall total (no grouping).
- GROUPING SETS simplifies complex reporting by combining multiple levels of aggregation into a single query.
- For example, in sales reports, you might use this query to summarize sales by year, region, and overall totals.
1. Purpose of the Query :
2. Key Components :
3. Why use GROUPING SETS? :
4. Real-World Application :
Additional Notes:
- GROUPING SETS is more efficient than running separate queries for each grouping level.
- Use this exercise to teach how to streamline multi-level aggregations.
For more Practice: Solve these Related Problems:
- Write a SQL query to calculate total revenue at multiple levels: by product, by region, and overall.
- Write a SQL query to summarize monthly and yearly sales data using GROUPING SETS.
- Write a SQL query to aggregate expenses by department and project, as well as provide an overall total.
- Write a SQL query to compute quarterly and annual profit margins using GROUPING SETS.
Go to:
PREV : Handling JSON Arrays in SQL Using OPENJSON.
NEXT : Transform Data Using PIVOT with Dynamic Columns.
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.