SQL Server ROLLUP

        
            
        
(d1, d2, d3)
(d1, d2)
(d1)
()

The ROLLUP is commonly used to calculate the aggregates of hierarchical data

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. 
  
 

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

Union All

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

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