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
Thanks a lot. Keep posting
good comment
I have the following table:
FormKey Flag1 Flag2 Flag3
———- —— —— ——
1 130 132 129
1 130 130 130
1 130 NULL NULL
2 132 NULL NULL
2 129 130 NULL
2 NULL 129 NULL
I would like to write a query/tsql script to get the following result:
(No cursor, no complicated loops and no multiple copy of same same record, this table is huge like to make it as fast as possible)
FormKey Flag1 Flag2 Flag3
———- —— —— ——
1 130 132 135
2 132 135 NULL
1. The Group By key is FormKey.
2. If the column has at least one record value as 132 then the final or rollup value should be 132 (all other value should be ignored).
3. If the column has all the values are 130 then the final or rollup value should be 130.
4. If the column has all the values are NULL then the final or rollup value should be NULL.
5. If the column has the values like 130,129, NULL and other than 132 the final or rollup value should be 135.
I need this ASAP.
Thanks in Advance for all your help and review.
hi i have a doubt using with roll up option can i rename the column value null to some other name like total or some other name please reply if yes then please reply it…..
it possible to
India Delhi NULL 29
where idia=total …hoe can i update total where sum(millions)
very good article on Roll up
Hi,
What is the best way to add another column that has the percentage of the population for each City, State?
Please help me to solve the sets.
X={a,2*b,c},Y={a,b,2*c}
and the values of a,b,c are: a=30,b=100,c=60
I want to find out how many X,Y and balances of a,b,c
Thanks