Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest freelancing marketplace for people like you.
By adding your name & email you agree to our terms, privacy and cookie policies.

# SQL CUBE Operator

The CUBE operator generates multiple grouping sets inside a GROUP BY.

CUBE generates subtotals across all column combinations specified in GROUP BY.

CUBE is similar to ROLLUP (see below).

### #

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

#### 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))
``````
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.

#### 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

Syntax of the CUBE function.

```GROUP BY CUBE(column1, column2, ..., columnn)
```

`columns` -- the columns for which `CUBE` generates grouping sets (dimensions).

## CUBE with 2 GROUP BY COLUMNS

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
``````
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

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

The first row is the grand total of all sales.

The next rows are total sales by product.

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
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

## You may also like

Last updated on Dec 21, 2023

Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest freelancing marketplace for people like you.
By adding your name & email you agree to our terms, privacy and cookie policies.