In this article we will go over basic understanding of Rollup clause in 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:
CREATE TABLE tblPopulation (
Country VARCHAR(100),
[State] VARCHAR(100),
City VARCHAR(100),
[Population (in Millions)] INT
)
GO
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.5)
INSERT INTO tblPopulation VALUES('India', 'Delhi','West Delhi',7.5)
INSERT INTO tblPopulation VALUES('India', 'Karnataka','Bangalore',9.5)
INSERT INTO tblPopulation VALUES('India', 'Karnataka','Belur',2.5)
INSERT INTO tblPopulation VALUES('India', 'Karnataka','Manipal',1.5)
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.5)
GO
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.
SELECT Country,[State],City, SUM ([Population (in Millions)]) AS [Population (in Millions)] FROM tblPopulation GROUP BY Country,[State],City WITH ROLLUP
Let me know if this explanation is easy to understand.
Reference: Pinal Dave (https://blog.sqlauthority.com)
68 Comments. Leave new
HI,
We get total fine…. How I can make “Total” Instead of “NULL” at end(Naming convention )
WITH MEM AS
(
SELECT ROW_NUMBER() OVER ( ORDER BY PRODUCTID) AS ROWNUMBER,PRODUCTNAME FROM PRODUCTS
)
SELECT * FROM MEM ORDER BY ROWNUMBER DESC
is possible to pass this query dynamically? Something like I can write
WITH MEM AS
(
EXECUTE(@STR)
)
Thanks dave … I am keep on reading your article … Very Interesting … Thanks for Posting …
How can I use Rollup/Cube/Grouping to the following o/p?
Age Gender Year DrugCov MedCov Members
0- 4 M 2006 Y N 2
0- 4 F 2006 N Y 5
0- 4 M 2006 Y Y 7
0- 4 F 2006 N N 3
0- 4 Want to show total here 17
5- 9 F 2007 Y N 4
5- 9 M 2007 N Y 8
5- 9 M 2007 Y Y 9
5- 9 F 2007 N N 2
5- 9 Want to show total here 23
Select AgeGroup, gender as Sex, [Year], DrugCov, MedCov, Sum(Member) as Members
From tbl
Group by AgeGroup, gender, Year, DrugCov, MedCov
Hi
Thanks for the article and comments. I learned few new things by this article.
Thanks
Superb Article :) Thank u so much:)
from this eg . i known the functionality of roll back .
thanks .
Hi, Panel Can you please write a query without roll up that shows the same result as you told it can be done.
thanks
hi, i have one…..
how to sort records while using the combination GroupBy….RollUp….Having
I know this is an old post, but as this blog features high on the Google results for my search, I thought I’d ask a question here.
Is there any way of using aggregate functions other than SUM() with a ROLLUP clause? It would be nice for the rollup to sometimes use MIN(), MAX() or AVG() while still showing all the detail rows…
This is a very useful clause in few cases.
Thanks for this article
Hi i have one table like the below format
slno member count
1 20
2 5001
3 250
4 4000
5 2500
i wnat the output format like this
slno member count row_number
1 20 1
2 5001 null
3 250 1
4 4000 1
5 2500 2
i want to get the row number if the member count less 5000. for each 5000 member count i need to give separate row number
please help me on this
Thanks
Sathiyamurthy.R
nice article
Very good & clear. Well Done!
Good Article! Helped me understand this concept better way than my book examples!
How to get the below output in sql server 2005
id Account deposit total
——————————
1 Vacation 10 10
2 Vacation 20 30
3 Vacation 30 60
4 Bills 40 40
5 Bills 50 90
6 Bills 60 150
7 Party 70 70
8 Party 80 150
without comparing the rows(b.id <= a.id)–(its taking too much time) . pls help
Thanks a lot. Keep posting
Easily understood the concept. Thanks a lot.
Excellent article