What is the purpose of ROLLUP function in SQL Server?

Answer:

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.

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

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.