With Rollup clause – SQL

Here I want share with you an interesting clause “With Rollup”

It will be very useful, when designing report with subtotal and grand totals. It allows executing multiple “hierarchy” or “levels’ of aggregation in a single SQL “Select Statement”.

“With Rollup” clause is only available from SQL 2005, and New versions

For example,

I have table named as “Student”, following script is a structure of this table

Create Table Student

(

StudentID int,

Student_Name Varchar(15),

SubjectName Varchar(15),

Marks_Scored numeric(18,1)

)

And I have inserted following student’s marks data,

Insert into Student Values(1,'Babu', 'Tamil',90)

Insert into Student Values(1,'Babu', 'English',78)

Insert into Student Values(1,'Babu', 'Maths',88)

Insert into Student Values(1,'Babu', 'Sciene',88)

Insert into Student Values(1,'Babu', 'Social Sciene',92)

Insert into Student Values(1,'Ramesh', 'Tamil',92)

Insert into Student Values(1,'Ramesh', 'English',88)

Insert into Student Values(1,'Ramesh', 'Maths',98)

Insert into Student Values(1,'Ramesh', 'Sciene',99)

Insert into Student Values(1,'Ramesh', 'Social Sciene',90)

Now We can Make Simple Aggregation Query:

select Student_Name,Sum(Marks_Scored) as Total

from Student group by Student_Name

This Query will returns,

Student_Name Total

Babu 436.0

Ramesh 467.0

Now If I want to display grand total, then I have to write script like this,

select Student_Name,Sum(Marks_Scored) as Total

from Student group by Student_Name

UNION ALL

select NULL,Sum(Marks_Scored) as Total

from Student

Output of this Query is,

Student_Name Total

Babu 436.0

Ramesh 467.0

NULL 903.0

Here we need only grand total, so we written the another aggregation query with "UNION ALL". also this query scans the whole table 2 times. It will reduce the performance.

We can do the same operation in better way by using WITH ROLLUP Clause

Have a look at this sample SQL script,

select Student_Name,Sum(Marks_Scored) as Total

from Student

group by Student_Name

with rollup

This query provides the same output with only a single scan:

Student_Name Total

Babu 436.0

Ramesh 467.0

NULL 903.0

We can also compute multiple ROLLUP levels in a single query.

For example, suppose that we want to displays student_name, Subject_Name, Marks_Scored, along with this Student wise total, Grand Total

This Query helps to implement multiple levels of aggregation.

Select

(Case When (Grouping(Student_Name)=1) Then 'G.Total' Else Student_Name End)as Student_Name,

(Case When (Grouping(SubjectName)=1) Then 'Total' Else SubjectName End)as SubjectName,

Sum(Marks_Scored) as Total

from Student group by Student_Name, SubjectName with rollup

Output of this Query:

Student_Name SubjectName Total

Babu English 78.0

Babu Maths 88.0

Babu Sciene 88.0

Babu Social Sciene 92.0

Babu Tamil 90.0

Babu Total 436.0

Ramesh English 88.0

Ramesh Maths 98.0

Ramesh Sciene 99.0

Ramesh Social Sciene 90.0

Ramesh Tamil 92.0

Ramesh Total 467.0

G.Total Total 903.0

This query makes easy our jobs, in reports also we no need to make groups, we can simply displays all the records with little touchup.

Hope it helps everyone. Please leave your valuable comments about this.