Grouping Sets is newly introduced in SQL Server 2008.
The use of Grouping Sets is that it simplifies the process of using group by on multiple columns one at a time.
Following are the steps that can be performed using a single Grouping Sets statement:
1) Group by on one column and calculate the result
2) Group by another column and calculate the result
3) Union the the result sets in steps 1 and 2
Following is the generic example for using Grouping Sets:
Let us consider a table @Invoice which contains the sales data with details of the product subproduct and amount. This table has some test data.
[sql]
DECLARE @Invoice TABLE(
Product varchar(50),
Subproduct varchar(50),
Amount int)
INSERT INTO @Invoice
SELECT ‘PEN’,’NIB PEN’,100
UNION
SELECT ‘PEN’,’NIB PEN’,200
UNION
SELECT ‘PEN’,’INK BRUSH’,200
UNION
SELECT ‘PEN’,’QUILL’,250
UNION
SELECT ‘PEN’,’QUILL’,500
UNION
SELECT ‘PEN’,’QUILL’,250
UNION
SELECT ‘PEN’,’REED PEN’,300
UNION
SELECT ‘PENCIL’,’Coloured pencil’,50
UNION
SELECT ‘PENCIL’,’Coloured pencil’,60
UNION
SELECT ‘PENCIL’,’Carbon pencil’,10
UNION
SELECT ‘ERASER’,’gum eraser’,20
UNION
SELECT ‘ERASER’,’gum eraser’,50
[/sql]
With the above table following will be the query to calculate the total sales for each product individually and the total sales for each subproduct individually:
[sql]
SELECT NULL AS Product,Subproduct, COUNT(*) FROM @Invoice
GROUP BY Subproduct
union all
SELECT Product,NULL AS Subproduct, COUNT(*) FROM @Invoice
GROUP BY Product
[/sql]
Output of the above query will be:
The same result set can be gained by using Grouping Sets as shown in the below query:
[sql]
SELECT Product,Subproduct, COUNT(*) FROM @Invoice
GROUP BY GROUPING SETS(Product,Subproduct)
[/sql]
If the result set has to be grouped by Product and Subproduct, Product, Subproduct
Following would be the union all statement:
[sql]
SELECT Product,Subproduct, COUNT(*) FROM @Invoice
GROUP BY Product,Subproduct
union all
SELECT NULL AS Product,Subproduct, COUNT(*) FROM @Invoice
GROUP BY Subproduct
union all
SELECT Product,NULL AS Subproduct, COUNT(*) FROM @Invoice
GROUP BY Product
[/sql]
This can be achieved simply by using grouping sets as shown below:
[sql]
SELECT Product,Subproduct, COUNT(*) FROM @Invoice
GROUP BY GROUPING SETS((Product,Subproduct),Product,Subproduct)
[/sql]