# SQL GROUPING SETS Operator

The GROUPING SETS operator generates multiple grouping sets inside a GROUP BY.

GROUPING SETS generate subtotals for each grouping specified.

GROUPING SETS are similar to CUBE and ROLLUP (see below).

GROUPING SETS offers the most flexibility with its custom groupings.

### #

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)
``````
Result:  3 records
Year Annual Sales
2012 226298.50
2013 658388.75
2014 469771.34

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),())
``````
Result:  4 records
Year Annual Sales
2012 226298.50
2013 658388.75
2014 469771.34
NULL 1354458.59

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

Syntax of the GROUPING SETS function.

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

`sets` -- the grouping sets for which `GROUPING SETS` generates subtotals.

## GROUPING SETS with 2 GROUP BY COLUMNS

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
``````
Result:  77 records
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

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
``````
Result:  107 records

The first row is the grand total of all sales.

Next are total sales by supplier.

Then total sales by supplier and product.

This is repeated for all suppliers.

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