Rollup in SQL Server

Suggested Videos
Part 99 - Table valued parameters in SQL Server
Part 100 - Send datatable as parameter to stored procedure
Part 101 - Grouping sets in SQL Server



ROLLUP in SQL Server is used to do aggregate operation on multiple levels in hierarchy. 



Let us understand Rollup in SQL Server with examples. We will use the following Employees table for the examples in this video.


Retrieve Salary by country along with grand total


There are several ways to achieve this. The easiest way is by using Rollup with Group By.

SELECT Country, SUM(Salary) AS TotalSalary

FROM Employees

GROUP BY ROLLUP(Country)

The above query can also be rewritten as shown below

SELECT Country, SUM(Salary) AS TotalSalary

FROM Employees

GROUP BY Country WITH ROLLUP

We can also use UNION ALL operator along with GROUP BY

SELECT Country, SUM(Salary) AS TotalSalary

FROM Employees

GROUP BY Country

UNION ALL

SELECT NULL, SUM(Salary) AS TotalSalary

FROM Employees

We can also use Grouping Sets to achieve the same result

SELECT Country, SUM(Salary) AS TotalSalary

FROM Employees

GROUP BY GROUPING SETS

(

    (Country),

    ()

)

Let's look at another example. 

Group Salary by Country and Gender. Also compute the Subtotal for Country level and Grand Total as shown below.


Using ROLLUP with GROUP BY

SELECT Country, Gender, SUM(Salary) AS TotalSalary

FROM Employees

GROUP BY ROLLUP(Country, Gender)

--OR

SELECT Country, Gender, SUM(Salary) AS TotalSalary

FROM Employees

GROUP BY Country, Gender WITH ROLLUP

Using UNION ALL with GROUP BY

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, NULL, SUM(Salary) AS TotalSalary

FROM Employees

Using GROUPING SETS

SELECT Country, Gender, SUM(Salary) AS TotalSalary

FROM Employees

GROUP BY GROUPING SETS

(

    (Country, Gender),

    (Country),

    ()

)