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

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

About these ads
  1. Emz
    September 1, 2011 at 10:28 am

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

    • sunny
      November 7, 2013 at 3:24 pm

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

  2. October 31, 2011 at 10:44 pm

    Just curious, is there any advantage of this syntax over the more commonly supported ROLLUP() function (DB2, Oracle, Sybase SQL Anywhere, SQL Server)?

    GROUP BY [Group], [Name] WITH ROLLUP
    — or
    GROUP BY ROLLUP([Group], [Name])

    • November 1, 2011 at 1:13 am

      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

      • November 1, 2011 at 2:50 am

        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…

  3. Bob H.
    February 4, 2012 at 3:41 am

    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.

    • February 23, 2012 at 7:59 pm

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

  4. Iliia Shterev
    June 2, 2012 at 5:23 am

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

  5. Quynh Nguyen
    September 10, 2012 at 6:26 am

    very useful article! Thanks!

  6. November 3, 2012 at 9:09 am

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

  7. January 31, 2013 at 2:14 pm

    great article simply i loved it

  8. January 31, 2013 at 2:20 pm

    great article i loved it

  9. ssk
    June 9, 2013 at 6:19 pm

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

  10. vivek
    June 13, 2013 at 11:08 am

    simply superb article…

  11. NOSQLExpert
    August 15, 2013 at 1:54 pm

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

  12. October 23, 2013 at 7:47 pm

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

    Thanks

  1. July 8, 2011 at 9:11 am
  2. July 8, 2011 at 9:13 pm
  3. July 12, 2011 at 9:02 pm
  4. October 7, 2011 at 2:56 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 270 other followers

%d bloggers like this: