SQL SERVER – Introduction to Rollup Clause

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

SQL SERVER - Introduction to Rollup Clause rollup1

Solarwinds

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

SQL SERVER - Introduction to Rollup Clause rollup2

Let me know if this explanation is easy to understand.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQLAuthority News – Blog Subscription and Comments RSS
Next Post
SQLAuthority News – MUGH – Microsoft User Group Hyderabad – Feb 2, 2010 Session Review

Related Posts

68 Comments. Leave new

  • HI,

    We get total fine…. How I can make “Total” Instead of “NULL” at end(Naming convention )

    Reply
  • 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)
    )

    Reply
  • Thanks dave … I am keep on reading your article … Very Interesting … Thanks for Posting …

    Reply
  • 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

    Reply
  • Hi

    Thanks for the article and comments. I learned few new things by this article.

    Reply
  • Yuvraj Gautam
    July 20, 2011 4:22 pm

    Thanks

    Reply
  • Raja Devarapu
    July 27, 2011 7:28 pm

    Superb Article :) Thank u so much:)

    Reply
  • from this eg . i known the functionality of roll back .

    thanks .

    Reply
  • Hi, Panel Can you please write a query without roll up that shows the same result as you told it can be done.

    thanks

    Reply
  • hi, i have one…..
    how to sort records while using the combination GroupBy….RollUp….Having

    Reply
  • 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…

    Reply
  • This is a very useful clause in few cases.
    Thanks for this article

    Reply
  • sathiyamurthy
    May 30, 2012 6:07 pm

    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

    Reply
  • nice article

    Reply
  • Surendra Kulkarni
    September 6, 2012 7:50 am

    Very good & clear. Well Done!

    Reply
  • Jeyakumar (@jeyakumarvs)
    December 22, 2012 8:20 am

    Good Article! Helped me understand this concept better way than my book examples!

    Reply
  • 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

    Reply
  • Thanks a lot. Keep posting

    Reply
  • Easily understood the concept. Thanks a lot.

    Reply
  • Excellent article

    Reply

Leave a Reply

Menu