Rollup in SQL Server

sql server group by rollup
sql server 2008 group by rollup
group by with rollup example
sql rollup example
sql server rollup example
sql server 2008 rollup examples
rollup clause in sql server
rollup function 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 GroupBy.
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),
()
)

Text version of the video
http://csharp-video-tutorials.blogspot.com/2015/09/rollup-in-sql-server.html

Slides
http://csharp-video-tutorials.blogspot.com/2015/09/rollup-in-sql-server_20.html

All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html

All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists