Usage of Rollup in Sql Server 2005

In this article we will go over basic understanding of Rollup clause in SQL Server. ROLLUP clause is used to do aggregate operation on multiple levels in hierarchy. Let us understand how it works by using an example.

Consider a table with the following structure and data:

CREATE TABLE tblPopulation (Country VARCHAR(100),[State] VARCHAR(100),City VARCHAR(100),[Population (in Millions)] INT)GOINSERT INTO tblPopulation VALUES('India', 'Delhi','East Delhi',9 )INSERT INTO tblPopulation VALUES('India', 'Delhi','South Delhi',8 )INSERT INTO tblPopulation VALUES('India', 'Delhi','North Delhi',5.5)INSERT INTO tblPopulation VALUES('India', 'Delhi','West Delhi',7.5)INSERT INTO tblPopulation VALUES('India', 'Karnataka','Bangalore',9.5)INSERT INTO tblPopulation VALUES('India', 'Karnataka','Belur',2.5)INSERT INTO tblPopulation VALUES('India', 'Karnataka','Manipal',1.5)INSERT INTO tblPopulation VALUES('India', 'Maharastra','Mumbai',30)INSERT INTO tblPopulation VALUES('India', 'Maharastra','Pune',20)INSERT INTO tblPopulation VALUES('India', 'Maharastra','Nagpur',11 )INSERT INTO tblPopulation VALUES('India', 'Maharastra','Nashik',6.5)GO

Now, we need to create a report on population at 3 levels: City, State and Country.

Can we do it by using SUM with GROUP BY clause?

Yes, we can, but we will have to write a separate query to GROUP BY at each level and then union the result of all queries; even after this, the proper ordering or the result would still need more work.

However, SQL Server provides a very easy solution. Just add the WITH ROLLUP clause in GROUP BY and you get the desired results.

SELECT Country,[State],City,SUM ([Population (in Millions)]) AS [Population (in Millions)]FROM tblPopulationGROUP BY Country,[State],City WITH ROLLUP