Rollup in SQL Server

Veröffentlicht am: 20 September 2015
auf dem Kanal: kudvenkat

126,378

911

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.

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
/ @aarvikitchen5572

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.blogspo...

Slides
http://csharp-video-tutorials.blogspo...

All SQL Server Text Articles
http://csharp-video-tutorials.blogspo...

All SQL Server Slides
http://csharp-video-tutorials.blogspo...

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenka...

All Dot Net and SQL Server Tutorials in Arabic
/ kudvenkatarabic

Auf dieser Seite können Sie das Online-Video Rollup in SQL Server mit der Dauer stunde minuten sekunde in guter Qualität ansehen, das der Benutzer kudvenkat 20 September 2015 hochgeladen hat, den Link mit Freunden und Bekannten teilen, dieses Video wurde auf Youtube bereits 126,378 Mal angesehen und es wurde von 911 den Zuschauern gefallen. Viel Spaß beim Betrachtenden Zuschauern gefallen!

1,019,015

21K