Cube in SQL Server

Cube() in SQL Server produces the result set by generating all combinations of columns specified in GROUP BY CUBE(). 

Let us understand Cube() in SQL Server with examples. We will use the following Employees table for the examples in this . 

Write a query to retrieve Sum of Salary grouped by all combinations of the following 2 columns as well as Grand Total.
Country,
Gender 

The output of the query should be as shown below


Using Cube with Group By

SELECT Country, Gender, SUM(Salary) AS TotalSalary

FROM Employees

GROUP BY Cube(Country, Gender)

--OR

SELECT Country, Gender, SUM(Salary) AS TotalSalary

FROM Employees

GROUP BY Country, Gender with Cube

The above query is equivalent to the following Grouping Sets query

SELECT Country, Gender, SUM(Salary) AS TotalSalary

FROM Employees

GROUP BY

    GROUPING SETS

    (

         (Country, Gender),

         (Country),

         (Gender),

         ()

    )

The above query is equivalent to the following UNION ALL query. While the data in the result set is the same, the ordering is not. Use ORDER BY to control the ordering of rows in the result set.

SELECT Country, Gender, SUM(Salary) AS TotalSalary

FROM Employees

GROUP BY Country, Gender

UNION ALL

SELECT Country, NULL, SUM(Salary) AS TotalSalary

FROM Employees

GROUP BY Country

UNION ALL

SELECT NULL, Gender, SUM(Salary) AS TotalSalary

FROM Employees

GROUP BY Gender

UNION ALL

SELECT NULL, NULL, SUM(Salary) AS TotalSalary

FROM Employees