{"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:
\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

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.
\n[sql]
\nDECLARE @Invoice TABLE(
\nProduct varchar(50),
\nSubproduct varchar(50),
\nAmount int)<\/p>\n

INSERT INTO @Invoice
\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

[\/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]
\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

Output of the above query will be:<\/p>\n

\"Grouping<\/a>
Grouping Sets<\/figcaption><\/figure>\n

The same result set can be gained by using Grouping Sets as shown in the below query:
\n[sql]
\nSELECT Product,Subproduct, COUNT(*) FROM @Invoice
\nGROUP BY GROUPING SETS(Product,Subproduct)
\n[\/sql]<\/p>\n

If the result set has to be grouped by Product and Subproduct, Product, Subproduct
\nFollowing would be the union all statement:
\n[sql]
\nSELECT Product,Subproduct, COUNT(*) FROM @Invoice
\nGROUP BY Product,Subproduct
\nunion all
\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

This can be achieved simply by using grouping sets as shown below:
\n[sql]
\nSELECT Product,Subproduct, COUNT(*) FROM @Invoice
\nGROUP BY GROUPING SETS((Product,Subproduct),Product,Subproduct)
\n[\/sql]<\/p>\n","protected":false},"excerpt":{"rendered":"

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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[41],"tags":[179,180,181],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/63"}],"collection":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/comments?post=63"}],"version-history":[{"count":0,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/63\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/media?parent=63"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/categories?post=63"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/tags?post=63"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}