SQL GROUPING SETS

GROUPING SETS is an extension of the GROUP BY clause that allows multiple groupings in a single query. Unlike ROLLUP and CUBE, which generate all possible subtotals, GROUPING SETS lets you specify custom groupings, making it more efficient for complex reports.

 

Syntax of SQL GROUPING SETS

SELECT column_name1, column_name2, aggregate_function(column_name)  
FROM table_name  
GROUP BY GROUPING SETS ( (column_name1), (column_name2), (column_name1, column_name2), () );  

• column_name1, column_name2: The columns used for grouping.
• aggregate_function(): Functions like SUM(), COUNT(), AVG(), MAX(), or MIN().
• GROUPING SETS(): Defines specific grouping combinations.
• () (empty set): Represents the grand total.

 

Key Features of SQL GROUPING SETS

• Allows custom grouping combinations instead of automatic ones.
• More efficient than CUBE and ROLLUP when not all subtotals are needed.
• Reduces unnecessary computations in reports.
• Useful for multi-dimensional reports.

 

Example of SQL GROUPING SETS

Consider the following table:

Table: Sales

Query: Compute total sales for each category, each product, and the grand total

SELECT category, product, SUM(sales_amount) AS total_sales  
FROM sales  
GROUP BY GROUPING SETS (  
    (category),       -- Total sales per category  
    (product),        -- Total sales per product  
    (category, product), -- Sales per category and product  
    ()                -- Grand total  
);  

Output:

category	product	total_sales
Electronics	Laptop	1000
Electronics	Phone	700
Electronics	NULL	1700
Furniture	Chair	500
Furniture	Table	800
Furniture	NULL	1300
NULL	Laptop	1000
NULL	Phone	700
NULL	Chair	500
NULL	Table	800
NULL	NULL	3000

Explanation

1. GROUP BY GROUPING SETS (category): Computes total sales per category.
2. GROUP BY GROUPING SETS (product): Computes total sales per product.
3. GROUP BY GROUPING SETS (category, product): Computes sales per category and product.
4. GROUP BY GROUPING SETS (): Computes the grand total for all rows.

 

SQL GROUPING SETS vs. SQL CUBE vs. SQL ROLLUP

Feature	GROUPING SETS	CUBE	ROLLUP
Custom grouping	✅	❌	❌
Generates all possible subtotals	❌	✅	❌
Generates grand total	✅	✅	✅
Best for large reports	✅	❌	✅

• CUBE: Generates all possible grouping combinations.
• ROLLUP: Generates a hierarchical grouping (e.g., category → product → total).
• GROUPING SETS: Allows specific custom grouping combinations.

 

Example with COUNT()

Query: Count the number of products in each category and overall

SELECT category, product, COUNT(*) AS product_count  
FROM sales  
GROUP BY GROUPING SETS (  
    (category),  
    (product),  
    (category, product),  
    ()  
);  

 

Summary

• GROUPING SETS allows customized subtotal calculations.
• It is more efficient than CUBE when only specific subtotals are needed.
• NULL values indicate subtotal or total rows.
• Use cases: Custom sales reports, financial analysis, and business intelligence.