SQL Reference

CUBE

CUBE is an extension of the SQL GROUP BY clause used with aggregate functions. It takes as input a list of columns and generates a result set equivalent to a union of simple GROUP BY clauses. The result set shows all possible combinations of groups defined by the input columns, including totals at multiple levels. It's especially useful for analyzing hierarchical data and producing complex reports in data warehousing and other business intelligence scenarios where multi-dimensional analysis is required.

Example

SELECT Country, Year, SUM(Sales) AS TotalSales
FROM SalesData
GROUP BY CUBE (Country, Year);

Output

Country	Year	TotalSales
USA	2018	100000
USA	2019	120000
UK	2018	90000
UK	2019	105000
NULL	2018	190000
NULL	2019	225000
USA	NULL	220000
UK	NULL	195000
NULL	NULL	415000

Explanation

In this query, CUBE is used to generate a result set that represents aggregates for all combinations of values in the selected columns. It produces the totals by Country, by Year, total for all Countries per year, total for all years per Country, and overall total. NULL represents the total across that column.

Example

SELECT City, Department, SUM(Salary)
FROM Empoyees
GROUP BY CUBE (City, Department);

Output

| City       | Department   | SUM(Salary) |
|------------|--------------|-------------|
| New York   | HR           |   21000     |
| London     | HR           |   20000     |
| New York   | Sales        |   30000     |
| London     | Sales        |   25000     |
| New York   | NULL         |   51000     |
| London     | NULL         |   45000     |
| NULL       | HR           |   41000     |
| NULL       | Sales        |   55000     |
| NULL       | NULL         |   96000     |

Explanation

In the given example, the CUBE function generates a result set that is similar to the one generated by the ROLLUP function, but with additional rows. The CUBE function creates subtotals for all possible combinations of the specified column or columns, not just the combinations that exist in the hierarchically ordered columns.

Example

SELECT Department, SUM(Salary) AS Total_Salary
FROM Employees
GROUP BY CUBE (Department);

Output

 Department | Total_Salary
------------+---------------
 IT         | 50000
 HR         | 70000
 Finance    | 55000
 NULL       | 175000

Explanation

The CUBE operator generates a result set that is the same as the one generated by the UNION ALL of the result sets of multiple GROUP BY queries. Here, we are aggregating Salary for each Department and including a row with the NULL department which gives us the total salary for all departments.