SQL ROLLUP Operator

To see how ROLLUP works, 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 ROLLUP

Now we add a ROLLUP operator, which generates subtotals accross columns in the GROUP BY operation, which in this case is just one, i.e. Year.

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

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

ROLLUP vs CUBE

Consider the grouping columns: column1 and column2.
Here are the possible grouping sets created by the two GROUP BY subclauses.

ROLLUP generates these grouping sets:

(column1, column2)
(column1)
()

CUBE generates these grouping sets:

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

The difference is that CUBE includes all possible grouping sets (dimensions).

Syntax of the ROLLUP function.

GROUP BY ROLLUP(column1, column2, ..., columnn)

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 ROLLUP

Now we add a ROLLUP operator. This adds subtotals for Supplier and Product combinations.

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 ROLLUP(CompanyName, ProductName)
 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 Grouping Sets Reference
• Our Sql CUBE Reference
• Our Sql PIVOT Reference