Home > SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > SQL Server – TSQL – GROUP BY WITH ROLLUP

SQL Server – TSQL – GROUP BY WITH ROLLUP


GROUP BY groups the result set into summary rows by provided columns. For example, consider below data which contains sales figures by region.

Group                Name                 SalesYTD

——————   ——————-  —————

North America        Northwest            123237.00

North America        Northwest             37534.00

North America        Northwest             48003.00

North America        Southwest            164232.00

North America        Southeast             39667.00

North America        Southeast            105810.00

Europe               France                74569.00

Europe               Germany               59456.00

Pacific              Australia             93403.00
Europe               United Kingdom        78327.00

 

This data can be summarized using a GROUP BY clause as below:

 

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT      [Group], [Name], SUM([SalesYTD]) AS 'Total Sales'

FROM        #TempTable

GROUP BY    [Group], [Name]

ORDER BY    [Group], [Name]

Result Set:

Group                Name                 Total Sales

——————   ——————   —————–

Europe               France                74569.00

Europe               Germany               59456.00

Europe               United Kingdom        78327.00

North America        Northwest            208774.00

North America        Southeast            145477.00

North America        Southwest            164232.00

Pacific              Australia             93403.00

 

(7 row(s) affected)

In the above result we can see that the data is summarized by [Group] and [Name].

 

WITH ROLLUP:

ROLLUP can be used to generate a subtotal rows and a grand total row for aggregate rows.

SELECT      [Group], [Name], SUM([SalesYTD]) AS 'Total Sales'

FROM        #TempTable

GROUP BY    [Group], [Name] WITH ROLLUP

Result Set:

Group                Name                 Total Sales

——————-  ——————-  ———————

Europe               France                74569.00

Europe               Germany               59456.00

Europe               United Kingdom        78327.00

Europe               NULL                 212352.00

North America        Northwest            208774.00

North America        Southeast            145477.00

North America        Southwest            164232.00

North America        NULL                 518483.00

Pacific              Australia             93403.00

Pacific              NULL                  93403.00

NULL                 NULL                 824238.00

 

(11 row(s) affected)

As we can see in the result set, 4 rows have been added, 3 for sub total of each [Group], and 1 row for grand total.

 

GROUPING():

GROUPING() function can be used to check whether the row is aggregated or not. It returns 1 for aggregated rows.

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT      [Group], [Name], SUM([SalesYTD])  AS 'Total Sales',

            GROUPING([Group]) AS 'Aggregated',

            GROUPING([Name]) AS 'Aggregated'

FROM        #TempTable

GROUP BY    [Group], [Name] WITH ROLLUP

Result Set:

Group                Name                 Total Sales   Aggregated    Aggregated

——————-  ——————– ————- ————  ————

Europe               France                74569.00     0             0

Europe               Germany               59456.00     0             0

Europe               United Kingdom        78327.00     0             0

Europe               NULL                 212352.00     0             1

North America        Northwest            208774.00     0             0

North America        Southeast            145477.00     0             0

North America        Southwest            164232.00     0             0

North America        NULL                 518483.00     0             1

Pacific              Australia             93403.00     0             0

Pacific              NULL                  93403.00     0             1

NULL                 NULL                 824238.00     1             1

 

(11 row(s) affected)

 

Finally, we can use GROUPING() function to identify and replace the 'NULL' in aggregated rows to something meaningful. for example, 'ALL'!!!!.

SELECT      CASE GROUPING([Group])

                  WHEN 1 THEN 'ALL'

                  ELSE [Group] END AS 'Group',

            CASE GROUPING([Name])

                  WHEN 1 THEN 'ALL'

                  ELSE [Name] END AS 'Name',

            SUM([SalesYTD])  AS 'Total Sales'

FROM        #TempTable

GROUP BY    [Group], [Name] WITH ROLLUP

Result Set:

Group                Name                 Total Sales

——————   ——————-  ——————–

Europe               France                74569.00

Europe               Germany               59456.00

Europe               United Kingdom        78327.00

Europe               ALL                  212352.00

North America        Northwest            208774.00

North America        Southeast            145477.00

North America        Southwest            164232.00

North America        ALL                  518483.00

Pacific              Australia             93403.00

Pacific              ALL                   93403.00

ALL                  ALL                  824238.00

 

(11 row(s) affected)

Hope This Helps!

Vishal

  1. Emz
    01.09.2011 10:28 | #1

    Explained here much better than the 70-433 book I am also using so thought I’d just say thanks. ?

  2. Vishal
    01.11.2011 01:13 | #2

    GROUP BY ROLLUP([Group], [Name]) should be used in development work. WITH ROLLUP and WITH CUBE are non-ISO compliant syntax and will be removed in a future version. More info here: http://technet.microsoft.com/en-us/library/ms177673.aspx

  3. lukaseder
    01.11.2011 02:50 | #3

    Thanks for the clarification. That’s what I thought. I was confused, though, because MySQL also has a WITH ROLLUP clause, but doesn’t support the ROLLUP(…) function…

  4. Bob H.
    04.02.2012 03:41 | #4

    I have a sales order table that’s joined to an account table. The requirement is to show all Accounts where the value of their sales orders is x% or more of the overall total. I am struggling how to turn the rollup values into something I can run calculations on, rather than display. Any suggestions?

    Thank you for this blog, you have gotten me further than anywhere else.

  5. Vishal
    23.02.2012 07:59 | #5

    sorry for the delayed response. you can use a cte:

    CREATE TABLE #TempTable
    ( Account VARCHAR(20),
    Sales NUMERIC(10,2)
    )
    INSERT INTO #TempTable VALUES ('North America', 10.00)
    INSERT INTO #TempTable VALUES ('North America', 20.00)
    INSERT INTO #TempTable VALUES ('North America', 30.00)
    INSERT INTO #TempTable VALUES ('North America', 40.00)
    INSERT INTO #TempTable VALUES ('North America', 50.00)
    INSERT INTO #TempTable VALUES ('South America', 10.00)
    INSERT INTO #TempTable VALUES ('South America', 20.00)
    INSERT INTO #TempTable VALUES ('South America', 30.00)
    INSERT INTO #TempTable VALUES ('South America', 40.00)
    INSERT INTO #TempTable VALUES ('South America', 50.00)

    ;WITH myCte
    AS
    (
    SELECT A.Account, A.Sales,
    Total = (SELECT SUM(B.Sales) FROM #TempTable B WHERE B.Account = A.Account)
    FROM #TempTable A
    )
    SELECT (Total * (30/100.0)), *
    FROM myCte
    WHERE Sales > (Total * (30/100.0))

  6. Iliia Shterev
    02.06.2012 05:23 | #6

    Totally great explanaion. Few words, great examples. Even I could undestand it ? . I do not think it can be any better. Thank you

  7. Quynh Nguyen
    10.09.2012 06:26 | #7

    very useful article! Thanks!

  8. Eugyn Sy
    03.11.2012 09:09 | #8

    Simple but clear, Thank You, More power to you.

  9. Mahesh Nagar
    31.01.2013 02:14 | #9

    great article simply i loved it

  10. ermahesh2009
    31.01.2013 02:20 | #10

    great article i loved it

  11. ssk
    09.06.2013 06:19 | #11

    great and simplified article..Please keep it up!!!

  12. vivek
    13.06.2013 11:08 | #12

    simply superb article…

  13. NOSQLExpert
    15.08.2013 01:54 | #13

    thanks alot!!!!! this article really saved my day and also has enlightened me.

  14. arnaudref
    23.08.2013 07:47 | #14

    Excellent,
    I didn’t know grouping. It ‘s very interesting to obtain var ‘ALL’

    Thanks

  15. sunny
    07.11.2013 03:24 | #15

    hey ..i just thought to take up 70-433 exam, its 2013.. is it fine to take up.. i am 2 plus exp

  16. fikremariam
    04.11.2014 05:06 | #16

    Thank you so much. Great help!!

  1. No trackbacks yet.