Let us learn ROLLUP function in SQL Server

Niladri.Biswas
Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 2406 red flag

In this article, we will understand the basic functionality of ROLLUP function.

Introduction

Roll Up is an SQL Server function that helps to perform aggregate operation on multiple levels in hierarchy.This on the other hand helps us to generate very handy reports.In this article, we will understand the basic functionality of this function with some examples.

SetUp for Experiment 1

Let us create a table with the below data

DECLARE @t TABLE([User Name] VARCHAR(50),[Access Date] DATE)

INSERT INTO @t VALUES
('Niladri','12/29/1996'),
('Arina','11/29/1986'),
('Niladri','12/29/2006'),
('Debasis','11/11/2005'),
('Arina','1/1/2013'),
('Rajlashmi','5/1/2013'),
('Rajlashmi','5/2/2013'),
('Rajlashmi','5/3/2013')

SELECT * FROM @t

Now , we need to generate a report as "How many users used till today and also the total sum of all".

For addressing the same let us fire the below query

SELECT 
		[User Name] = CASE WHEN GROUPING([User Name]) = 1 THEN 'Total:'
		ELSE [User Name] END
		,[Count] = COUNT ([User Name])
FROM @t
GROUP BY [User Name] WITH ROLLUP

The output is as under

As can be figure out that, we have count for the users at every individual levels as well as the total count.ROLLUP adds new row for each column used in GROUP BY clause.

SetUp for Experiment 2

I have a table as under

I want the output to be as

Let us generate the DDL for this

DECLARE @t TABLE([Script_Type] VARCHAR(10),[detail_warnings] VARCHAR(100))
INSERT INTO @t 
Select script_Type = 'SP',detail_warnings ='Consider using EXISTS predicate instead of IN predicate' UNION ALL
Select script_Type = 'SP',detail_warnings ='ExcludeItem does not exist in database SQLEye or is invalid for this operation' UNION ALL
Select script_Type='SP',detail_warnings ='Values hardcoded in where-clause condition' UNION ALL
Select script_Type='Table',detail_warnings ='Table name is not singular  Remarks :1:- Missing create index statement.' UNION ALL
Select script_Type='Table',detail_warnings ='Check for existence object then Drop statement before create statement' UNION ALL
Select script_Type='View',detail_warnings ='Invalid name'

Let us fire the below query

SELECT Script_Type,Detail_Warnings,Error_Count = COUNT(script_Type)
FROM  @t 
GROUP BY ROLLUP(script_Type,detail_warnings)

As can be figure out that the ROLL UP function has done most of the work for us.Only thing is the formatting part.If we observer carefully,there are two kinds of patterns here.One is where the "Script_Type" column has value and "Details_Warnings" is "NULL" and the second is where both are "NULL".Now a little of tweaking with the below code will help us to get the desired result.Basically we need to deal with the various ROLLUP rows in the SELECT.

SELECT 
	Script_Type = 
			CASE
				WHEN script_Type IS NULL AND detail_warnings IS NULL THEN 'Total'
				WHEN detail_warnings IS NULL THEN script_Type + ' Count'
				ELSE script_Type 
			END
  ,Detail_Warnings = ISNULL(detail_warnings, '')
  ,Error_Count = COUNT(script_Type)
FROM  @t 
GROUP BY ROLLUP(script_Type,detail_warnings)

So what we are doing here is that, where ever we are finding the "Script_Type" column has value and "Details_Warnings" is "NULL", we are setting the "Script_Type" column value as "script_Type + ' Count'".For the other case we are setting the value as "Total".

References

GROUP BY, CUBE, ROLLUP and SQL SERVER 2005

Conclusion

Hope this article has helped us in understanding the usage of ROLLUP function and how efficiently we can generate reports using this.Thanks for reading.

Page copy protected against web site content infringement by Copyscape

About the Author

Niladri.Biswas
Full Name: Niladri Biswas
Member Level: Platinum
Member Status: Member
Member Since: 10/25/2010 11:04:24 AM
Country: India
Best Regards, Niladri Biswas
http://www.dotnetfunda.com
Technical Lead at HCL Technologies

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)