SQL GROUPING SETS Operator

To see how GROUPING SETS work, we'll start with a GROUP BY operation.
This example groups total sales by year.

SELECT YEAR(OrderDate) AS Year,
       SUM(TotalAmount) AS 'Annual Sales'
  FROM [Order]
 GROUP BY YEAR(OrderDate)
 ORDER BY YEAR(OrderDate)

Next: Adding GROUPING SETS

Now we add the GROUPING SETS operator, which generates subtotals for a GROUP BY operation. In this case two grouping sets specified are: year and the grand total.

SELECT YEAR(OrderDate) AS Year,
       SUM(TotalAmount) AS 'Annual Sales'
  FROM [Order]
 GROUP BY GROUPING SETS(YEAR(OrderDate),())

This adds a summary row representing total sales over all years.

GROUPING SETS vs ROLLUP vs CUBE

Consider the grouping columns: column1 and column2.
Here are the possible grouping sets based on these three GROUP BY subclauses.

GROUPING SETS generates a custom set of groupings from this list:

(column1, column2)
(column1)
(column2)
()

ROLLUP generates these grouping sets:

(column1, column2)
(column1)
()

CUBE generates these grouping sets:

(column1, column2)
(column1)
(column2)
()

ROLLUP and CUBE have fixed groupings, whereas GROUPING SETS allows custom groupings.

Syntax of the GROUPING SETS function.

GROUP BY GROUPING SETS(set1, set2, ..., setn)

Again, we start with a GROUP BY statement.
This example groups total sales by supplier and product.

SELECT CompanyName, ProductName,
       SUM(I.UnitPrice * I.Quantity) AS 'Total Sales'
  FROM Supplier S
  JOIN Product P ON S.Id = P.SupplierId
  JOIN OrderItem I ON P.Id = I.ProductId
 GROUP BY CompanyName, ProductName
 ORDER BY CompanyName, ProductName

CompanyName	ProductName	Total Sales
Aux joyeux ecclésiastiques	Chartreuse verte	13150.80
Aux joyeux ecclésiastiques	Côte de Blaye	149984.20
Bigfoot Breweries	Laughing Lumberjack Lager	2562.00
Bigfoot Breweries	Sasquatch Ale	6678.00
Bigfoot Breweries	Steeleye Stout	14536.80
Cooperativa de Quesos 'Las Cabras'	Queso Cabrales	13902.00
Cooperativa de Quesos 'Las Cabras'	Queso Manchego La Pastora	12866.80
Escargots Nouveaux	Escargots de Bourgogne	6664.75
Exotic Liquids	Aniseed Syrup	3080.00
Exotic Liquids	Chai	14277.60

Next: Adding GROUPING SETS

Now we add a GROUPING SETS operator. This adds subtotals for Supplier/Product combinations and a grand total for all sales.

SELECT CompanyName, ProductName,
       SUM(I.UnitPrice * I.Quantity) AS 'Total Sales'
  FROM Supplier S
  JOIN Product P ON S.Id = P.SupplierId
  JOIN OrderItem I ON P.Id = I.ProductId
 GROUP BY GROUPING SETS((CompanyName, ProductName), (CompanyName),())
 ORDER BY CompanyName, ProductName

CompanyName	ProductName	Total Sales
NULL	NULL	1354458.59
Aux joyeux ecclésiastiques	NULL	163135.00
Aux joyeux ecclésiastiques	Chartreuse verte	13150.80
Aux joyeux ecclésiastiques	Côte de Blaye	149984.20
Bigfoot Breweries	NULL	23776.80
Bigfoot Breweries	Laughing Lumberjack Lager	2562.00
Bigfoot Breweries	Sasquatch Ale	6678.00
Bigfoot Breweries	Steeleye Stout	14536.80
Zaanse Snoepfabriek	NULL	5901.35
Zaanse Snoepfabriek	Chocolade	1542.75
Zaanse Snoepfabriek	Zaanse koeken	4358.60

• Our Sql ROLLUP Reference
• Our Sql CUBE Reference
• Our Sql PIVOT Reference