CUBE

SQL Server

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.

Oracle

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.

PostgreSQL

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.