Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

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

No comments:

Post a Comment

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.