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:

1. Purpose of the Query :

1. The goal is to demonstrate how to use GROUPING SETS to perform multi-level aggregation in a single query.

2. Key Components :

1. GROUPING SETS : Allows multiple grouping combinations in one query.
2. (Year, Region) : Groups by both year and region.
3. (Year) : Groups by year only.
4. () : Represents the overall total (no grouping).

3. Why use GROUPING SETS? :

1. GROUPING SETS simplifies complex reporting by combining multiple levels of aggregation into a single query.

4. Real-World Application :

1. For example, in sales reports, you might use this query to summarize sales by year, region, and overall totals.

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.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Handling JSON Arrays in SQL Using OPENJSON.
Next SQL Exercise: Transform Data Using PIVOT with Dynamic Columns.