{"id":63,"date":"2011-08-04T11:49:12","date_gmt":"2011-08-04T11:49:12","guid":{"rendered":"https:\/\/sqlserverlearner.azurewebsites.net\/2011\/08\/04\/using-grouping-sets-with-group-by-clause\/"},"modified":"2011-08-04T11:49:12","modified_gmt":"2011-08-04T11:49:12","slug":"using-grouping-sets-with-group-by-clause","status":"publish","type":"post","link":"https:\/\/sqlserverlearner.com\/2011\/08\/04\/using-grouping-sets-with-group-by-clause\/","title":{"rendered":"Using Grouping Sets with Group By Clause Example"},"content":{"rendered":"
Grouping Sets<\/strong> is newly introduced in SQL Server 2008.<\/p>\n The use of Grouping Sets is that it simplifies the process of using group by on multiple columns one at a time.<\/p>\n Following are the steps that can be performed using a single Grouping Sets statement: Following is the generic example for using Grouping Sets:<\/p>\n Let us consider a table @Invoice<\/strong> which contains the sales data with details of the product subproduct and amount. This table has some test data. INSERT INTO @Invoice [\/sql]<\/p>\n 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:<\/p>\n [sql] Output of the above query will be:<\/p>\n
\n1) Group by on one column and calculate the result
\n2) Group by another column and calculate the result
\n3) Union the the result sets in steps 1 and 2<\/p>\n
\n[sql]
\nDECLARE @Invoice TABLE(
\nProduct varchar(50),
\nSubproduct varchar(50),
\nAmount int)<\/p>\n
\nSELECT ‘PEN’,’NIB PEN’,100
\nUNION
\nSELECT ‘PEN’,’NIB PEN’,200
\nUNION
\nSELECT ‘PEN’,’INK BRUSH’,200
\nUNION
\nSELECT ‘PEN’,’QUILL’,250
\nUNION
\nSELECT ‘PEN’,’QUILL’,500
\nUNION
\nSELECT ‘PEN’,’QUILL’,250
\nUNION
\nSELECT ‘PEN’,’REED PEN’,300
\nUNION
\nSELECT ‘PENCIL’,’Coloured pencil’,50
\nUNION
\nSELECT ‘PENCIL’,’Coloured pencil’,60
\nUNION
\nSELECT ‘PENCIL’,’Carbon pencil’,10
\nUNION
\nSELECT ‘ERASER’,’gum eraser’,20
\nUNION
\nSELECT ‘ERASER’,’gum eraser’,50<\/p>\n
\nSELECT NULL AS Product,Subproduct, COUNT(*) FROM @Invoice
\nGROUP BY Subproduct
\nunion all
\nSELECT Product,NULL AS Subproduct, COUNT(*) FROM @Invoice
\nGROUP BY Product
\n[\/sql]<\/p>\n