Vpire Blog

In this article we will go over basic understanding of Rollup clause in Oracle and 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:

MSSQL

CREATE TABLE tblPopulation (
Country VARCHAR(100),
[State] VARCHAR(100),
City VARCHAR(100),
Population INT
)
GO

Oracle


CREATE TABLE tblPopulation (
Country VARCHAR(100),
State VARCHAR(100),
City VARCHAR(100),
Population INT
)


INSERT 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)
INSERT INTO tblPopulation VALUES('India', 'Delhi','West Delhi',7)
INSERT INTO tblPopulation VALUES('India', 'Karnataka','Bangalore',9)
INSERT INTO tblPopulation VALUES('India', 'Karnataka','Belur',2)
INSERT INTO tblPopulation VALUES('India', 'Karnataka','Manipal',1)
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)

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.

MSSQL

SELECT Country,[State],City,
SUM (Population)
FROM tblPopulation
GROUP BY Country,[State],City WITH ROLLUP

Oracle

SELECT Country,[State],City,
SUM (Population)
FROM tblPopulation
GROUP BY ROLLUP(Country,State,City);

Bookmark It

Hide Sites