SQL CUBE Operator

To see how CUBE 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 CUBE

Now, we add a CUBE operator, which generates subtotals accross all 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 CUBE(YEAR(OrderDate))

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

CUBE vs ROLLUP

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

CUBE generates these grouping sets:

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

ROLLUP generates these grouping sets:

(column1, column2)
(column1)
()

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

Syntax of the CUBE function.

GROUP BY CUBE(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 CUBE

Now we add a CUBE operator. This adds subtotals for all 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 CUBE(CompanyName, ProductName)
 ORDER BY CompanyName, ProductName

CompanyName	ProductName	Total Sales
NULL	NULL	1354458.59
NULL	Alice Mutton	35482.20
NULL	Aniseed Syrup	3080.00
NULL	Boston Crab Meat	19048.30
NULL	Camembert Pierrot	50286.00
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 ROLLUP Reference
• Our Sql PIVOT Reference