How do you use the GROUPING SETS clause to generate multiple levels of aggregations in a single query?
LeoRobs
Posted by LeoRobs
Last Updated: July 29, 2024
GROUPING Aggregation
The GROUPING SETS clause in SQL is a powerful tool that allows you to create multiple levels of aggregations in a single query. It enables you to specify exactly which grouping combinations you want to compute, thereby reducing the need for multiple separate queries or union operations. Here's a general structure of how you can use GROUPING SETS in a SQL query:
Syntax
SELECT 
    column1,
    column2,
    aggregate_function(column3)
FROM 
    your_table
GROUP BY 
    GROUPING SETS (
        (column1, column2),     -- Grouping by both column1 and column2
        (column1),              -- Grouping by column1 only
        (column2),              -- Grouping by column2 only
        ()                       -- Grand total (no grouping at all)
    );
Example
Suppose you have a sales table that contains sales data with columns for salesperson, region, and amount. You want to get the total sales amounts grouped by salesperson, by region, and a grand total. 
SELECT 
    salesperson,
    region,
    SUM(amount) AS total_sales
FROM 
    sales
GROUP BY 
    GROUPING SETS (
        (salesperson, region),  -- Total by salesperson and region
        (salesperson),          -- Total by salesperson
        (region),               -- Total by region
        ()                       -- Overall total
    );
Explanation of the Example
In this query: - The first grouping set (salesperson, region) calculates total sales for each combination of salesperson and region. - The second grouping set (salesperson) gives the total sales for each salesperson across all regions. - The third grouping set (region) gives the total sales for each region across all salespeople. - The empty parentheses () calculate the grand total of all sales.
Grouping Columns Indication
When you execute this query, the results will include NULLs in the columns that weren't part of a specific grouping set. To distinguish the various levels in your result set, you can make use of the GROUPING function, which shows whether a column is part of a grouping or not: 
SELECT 
    GROUPING(salesperson) AS is_salesperson_grouped,
    GROUPING(region) AS is_region_grouped,
    salesperson,
    region,
    SUM(amount) AS total_sales
FROM 
    sales
GROUP BY 
    GROUPING SETS (
        (salesperson, region),
        (salesperson),
        (region),
        ()
    );
Conclusion
Using GROUPING SETS helps in efficiently generating complex aggregated reports without the need for multiple queries. It simplifies the SQL code and enhances performance by avoiding unnecessary data scans. This is particularly useful in business intelligence and reporting scenarios where multiple aggregation levels are needed.
Default Image