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
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
[User Name] = CASE WHEN GROUPING([User Name]) = 1 THEN 'Total:'
ELSE [User Name] END
,[Count] = COUNT ([User Name])
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.
Asked In: Many Interviews |