HI WELCOME TO SIRIS

Rollup in SQL Server

Leave a Comment

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.
Employees Table

Retrieve Salary by country along with grand total
sql server group by with rollup

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.
sql server 2008 group by with rollup

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

0 comments:

Post a Comment

Note: only a member of this blog may post a comment.