SQL Server – TSQL – GROUP BY GROUPING SETS()
By using GROUPING SETS() we can specify multiple groupings in a single query. GROUPING SETS() generates the result by producing a UNION ALL set of the result sets generated by specified grouping sets.
for example, consider below data:
– © 2011 – Vishal (http://SqlAndMe.com)
SELECT [Group], [Name], [SalesYTD]
FROM dbo.Sales
Result Set:
Group Name SalesYTD
—————————— —————————— —————————————
North America Northwest 123237.00
South America Northwest 37534.00
South America Southwest 39667.00
North America Southwest 164232.00
(4 row(s) affected)
To summarize this data we can use GROUP BY, to summarize by more that one set we need multiple SELECTs, and UNION ALL to merge the result set:
SELECT [Group], [Name], SUM(SalesYTD) AS 'Total Sales'
FROM dbo.Sales
GROUP BY [Group], [Name]
UNION ALL
SELECT [Group], NULL, SUM(SalesYTD) AS 'Total Sales'
FROM dbo.Sales
GROUP BY [Group]
UNION ALL
SELECT NULL, NULL, SUM(SalesYTD) AS 'Total Sales'
FROM dbo.Sales
Result Set:
Group Name Total Sales
—————————— —————————— —————————————
North America Northwest 123237.00
South America Northwest 37534.00
North America Southwest 164232.00
South America Southwest 39667.00
North America NULL 287469.00
South America NULL 77201.00
NULL NULL 364670.00
(7 row(s) affected)
The above result set can be achieved by a single GROUPING SETS() clause as below:
– WITH ROLLUP Equivalent
SELECT [Group], [Name], SUM(SalesYTD) AS 'Total Sales'
FROM dbo.Sales
GROUP BY GROUPING SETS (([Group], [Name]), ([Group]), ())
As the sets we have specified generate sub-total and a grand-total row, the same result set can be produced by using GROUP BY ROLLUP().
The empty set ’( )‘ in GROUPING SETS() generates grand total.
GROUPING SETS() can also be used to generate results produced by GROUP BY CUBE(), since we can specify all the sets generated by GROUP BY CUBE().
SELECT [Group], [Name], SUM(SalesYTD) AS 'Total Sales'
FROM dbo.Sales
GROUP BY CUBE ([Group], [Name])
– WITH CUBE Equivalent
SELECT [Group], [Name], SUM(SalesYTD) AS 'Total Sales'
FROM dbo.Sales
GROUP BY GROUPING SETS (([Group], [Name]), ([Group]), ([Name]), ())
Result Set (2nd):
Group Name Total Sales
—————————— —————————— —————————————
North America Northwest 123237.00
South America Northwest 37534.00
NULL Northwest 160771.00
North America Southwest 164232.00
South America Southwest 39667.00
NULL Southwest 203899.00
NULL NULL 364670.00
North America NULL 287469.00
South America NULL 77201.00
(9 row(s) affected)
Let’s take another example, if you need to generate a pivot table based on sales data, you will need to group data by multiple sets:
|
Product |
Year |
|
|||
|
|
2008 |
1009 |
2010 |
2011 |
Total |
|
Product 1 |
GROUP BY (Product, Year) |
GROUP BY (Product) |
|||
|
Product 2 |
|||||
|
Product 3 |
|||||
|
ALL |
GROUP BY (Year) |
GROUP BY () |
|||
To generate this pivot, we need to group results by four different sets, This can be written using T-SQL as below:
– Equivalent to GROUP BY CUBE
SELECT [Product], [Year], SUM(SalesYTD) AS 'Total Sales'
FROM dbo.ProductSales
GROUP BY GROUPING SETS
( ([Product], [Year]),
([Product]),
([Year]),
()
)
from http://sqlandme.com/2011/07/12/sql-server-tsql-group-by-grouping-sets/
