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:

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]),
      ()
)

Hope This Helps! Cheers!