SQL Server ROLLUP

The SQL Server ROLLUP is a subclause of the GROUP BY clause which provides a shorthand for defining multiple grouping sets.

ROLLUP(d1,d2,d3) creates only four grouping sets, assuming the hierarchy d1 > d2 > d3, as follows:


        
(d1, d2, d3)
(d1, d2)
(d1)
()
The <code>ROLLUP</code> is commonly used to calculate the aggregates of hierarchical data

Let us understand Rollup in SQL Server with examples. We will use the following <strong>Employees table</strong> for the examples in this video. 
 <img alt="Employees Table" src="https://2.bp.blogspot.com/-XY5knCMZHbs/Vf5W2psPhAI/AAAAAAAAeeU/EfRIYVumR3M/s1600/Employees%2BTable.png"> 
 

Retrieve Salary by country along with grand total 
 <img alt="sql server group by with rollup" src="https://1.bp.blogspot.com/-cXg8JUXengo/Vf5XIIymtBI/AAAAAAAAeec/1yxES1yAS0k/s1600/sql%2Bserver%2Bgroup%2Bby%2Bwith%2Brollup.png"> 
 
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. 
 <img alt="sql server 2008 group by with rollup" src="https://4.bp.blogspot.com/-KS_m6vzX_28/Vf5X4KGfifI/AAAAAAAAeek/pD252q9P_Fo/s1600/sql%2Bserver%2B2008%2Bgroup%2Bby%2Bwith%2Brollup.png">

(d1, d2, d3)

(d1, d2)

(d1)

()

The <code>ROLLUP</code> is commonly used to calculate the aggregates of hierarchical data

Let us understand Rollup in SQL Server with examples. We will use the following <strong>Employees table</strong> 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.

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),
     ()
 )

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

()

)

Union All


SELECT Country, NULL, SUM(Salary) AS TotalSalary
 FROM Employees
 GROUP BY Country

SELECT Country, NULL, SUM(Salary) AS TotalSalary

FROM Employees

GROUP BY Country

Using UNION ALL with GROUP BY


 SELECT Country, Gender, SUM(Salary) AS TotalSalary
 FROM Employees
 GROUP BY Country, Gender

SELECT Country, Gender, SUM(Salary) AS TotalSalary

FROM Employees

GROUP BY Country, Gender

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