Generating SubTotals using GROUPING

  • Sunil Chandurkar

    Right there with Babe

    Points: 791

    Comments posted to this topic are about the item Generating SubTotals using GROUPING

  • R.P.Rozema

    SSChampion

    Points: 12300

    When you want to identify the "scheme totals"-rows in your result set you need to add another GROUPING() column, this time specifying the Scheme_Name column for its parameter.

    SELECT

    ISNULL(Product_Name,'ZProductTotal') as Product_name,

    ISNULL(Scheme_Name,'ZTotal') as Scheme_Name,

    SUM(Invest_Amount) AS [Invest_Amount],

    SUM(broker_commission) AS [Broker_Commission],

    GROUPING(Scheme_Name) AS [IsSchemeTotal],

    GROUPING(Product_Name) AS [IsGrandTotal]

    FROM @GroupTest

    GROUP BY Product_Name,Scheme_Name

    WITH ROLLUP

    ORDER BY Product_Name,Scheme_Name, IsSchemeTotal, IsGrandTotal

    An excerpt of the output is now:

    Product_name Scheme_Name Invest_Amount Broker_Commission IsSchemeTotal IsGrandTotal

    ...

    Product9 Scheme5 480107.25 39160.80 0 0

    Product9 ZTotal 5304042.00 432633.60 1 0

    ZProductTotal ZTotal 24747623.55 2018583.84 1 1

    The first row in the excerpt shows the figures for Product9 in Scheme5, both grouping columns are 0, indicating this row is not a (sub-)totals row. On the 2nd row the IsSchemeTotal column returns 1, indicating this rows holds the sub-total for all schemes for Product9. On the 3rd row you see both IsSchemeTotal and IsGrandTotal set to 1, indicating this row holds the figures for both all schemes and all products, i.e. the grand total.

    As an extra note: if you change the order by clause to ORDER BY IsGrandTotal, Product_Name, IsSchemeTotal, Scheme_Name, you'l always get the 'normal' rows before the subtotals before the grand totals.



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Good article, andd good addition by R.P.Rozema.

    Remember that WITH ROLLUP is deprecated as of SQL Server 2010. It has been replaced with GROUPING_SETS.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Fiacre Lenehan

    Mr or Mrs. 500

    Points: 584

    With Rollup can be replaced with Rollup

    SELECT

    Product_Name,

    Scheme_Name,

    Invest_Amount,

    broker_commission,

    -1 AS IsSchemeTotal,

    -1 as IsGrandTotal

    FROM @GroupTest

    Union ALL

    select

    isnull(Product_Name, 'ZProductTotal'),

    isnull(Scheme_Name,'ZTotal'),

    sum(Invest_Amount),

    SUM(Broker_Commission),

    GROUPING(Scheme_Name) AS IsSchemeTotal,

    GROUPING(Product_Name) AS IsGrandTotal

    from @grouptest

    group by rollup (Product_Name,Scheme_Name)

    ORDER BY Product_Name,

    Scheme_Name,

    IsSchemeTotal,

    IsGrandTotal

    To give the same results

  • Trey Staker

    SSCarpal Tunnel

    Points: 4736

    I enjoyed your article. Thank you!

    ---------------------------------------------------------------------
    Use Full Links:
    KB Article from Microsoft on how to ask a question on a Forum

  • Mike Dougherty

    Ten Centuries

    Points: 1112

    Rather than union all of 4 separate selects against the same base table, would 4 CTE based on each other be better performing? On SQL2000 (no CTE) we tend to run a lot into temp tables. In this scenario it usually makes sense to aggregate on the aggregated table so each select processes less source rows. ( million detail rows, 100k daily, 20k weekly, 5k monthly, etc ) Why process the million detail rows 4 times?

  • loki1049

    SSCrazy

    Points: 2065

    Here is another good definition of using this style of groups

  • Eric L Hackett

    SSC Eights!

    Points: 978

    How would one go about grouping by dates, i.e. month, year?

    So a query from 12/30/2009 thru 1/2/2010 would have something similar to this.

    Meter Day Month Year Barrels

    ```````````````````````````````````````

    Meter1 12/30/09 Dec 2009 5

    Meter1 12/31/09 Dec 2009 10

    Meter1 DecTotal 2009 15

    Meter1 2009Total 15

    Meter2 1/1/10 Jan 2010 8

    Meter2 1/2/10 Jan 2010 12

    Meter2 JanTotal 2010 20

    Meter2 2010Total 20

  • john.arnott

    SSChampion

    Points: 11882

    Wow. Did I mess up on this one. Let me correct myself before someone else has to.

    The answer to Sunil's question about why only the grand total line got a 1 indicator is quite simple. The GROUPING column was defined to indicate rows added by ROLLUP for a total of all Product_Name subtotals. To get an indcator for those subtotals, we'd have to add another column, GROUPING(Scheme_Name), which will indicate rows added by ROLLUP for totals of all Scheme_Names in a Product_Name.

    SELECT Product_Name,

    Scheme_Name,

    Invest_Amount,

    broker_commission,

    -1 AS GrandTotal,

    -1 as ProductTotal --<=== Added this

    FROM @GroupTest

    UNION ALL

    SELECT ISNULL(Product_Name,'ZProductTotal'),

    ISNULL(Scheme_Name,

    'ZTotal'),

    SUM(Invest_Amount) AS 'Invest_Amount',

    SUM(broker_commission) AS 'Broker_Commission',

    GROUPING(Product_Name)'GrandTotal',

    GROUPING(Scheme_Name)'ProductTotal' --<==== added this

    FROM @GroupTest

    GROUP BY Product_Name,

    Scheme_Name

    WITH ROLLUP

    ORDER BY Product_Name,

    Scheme_Name,

    GrandTotal

    From the article:

    Grouping separates the NULL values that are returned by ROLL UP from normal null values. In the above example (Method 2) the Totals and the Grand Total rows are actually generated by the ROLL UP operation. ROLL UP adds the NULL value for the summary row. Grouping separates this NULL by indicating 1 against the row. Hence in the above output (Method 2) the Grand Total summary row is indicated by a 1.

    Here one question comes in the mind that why only one row is indicated by 1? The Scheme_Total and Product_Total is also aggregated but it is not indicated by 1. I have not yet received any satisfactory answer for this. I will post the answer here once I get it.

    Let's start over with what GROUPING does. MSDN tells us that GROUPING is "... an aggregate function that causes an additional column to be output with a value of 1 when the row is added by either the CUBE or ROLLUP operator, or 0 when the row is not the result of CUBE or ROLLUP."

    This is wrong. Let me strike it out.....

    Now we can answer the question as to why only one row gets the GROUPING indcator of 1 by recognizing that it is the only row that's inserted into the result set by the ROLLUP. The Product_Total amounts are indeed aggregated, but that's done by the GROUP BY, not by the ROLLUP.

  • SQLRNNR

    SSC Guru

    Points: 281210

    Thanks for the article.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Ameya- Ameyask

    SSC Veteran

    Points: 203

    Very useful information especially for the report writers.

    Thanks,

    Ameya

  • vpatel1981

    SSC Enthusiast

    Points: 184

    Nice article, thank you.

  • sree1119

    SSC Rookie

    Points: 39

    What is the difference between "With Cube" and "Rollup"?

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    sree1119 (7/14/2010)


    What is the difference between "With Cube" and "Rollup"?

    If you use GROUP BY Product_Name,Scheme_Name WITH ROLLUP (sorry for using the deprecated syntax; I don't have a Books Online 2008 at hand to check the new syntax), you get these rows:

    * One for each unique Product_Name, Scheme_Name combination.

    * One for each unique Product_Name (for the totals over all schemes for that product)

    * One for the grand total

    If you reverse it to GROUP BY Scheme_Name, Product_Name WITH ROLLUP, you will lose the rows with the totals per product over all schemes, but instead you will get rows with the totals per scheme over all products.

    If you use WITH CUBE instead of WITH ROLLUP, you will get all these variations.

    So basically, the WITH CUBE produces all subtotals over zero, one or more of the GROUP BY columns, and the WITH ROLLUP reduces that to only subtotals over no GROUP BY columnss, the first only, first and second, first to third, etc.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Daniel Bowlin

    SSC-Dedicated

    Points: 34566

    Good article. I am going to have to spend some more time reading about all the permutations of Grouping: Rollup, Cube and Grouping Sets. I do a lot of reports, and I can see a lot of useful applications for this.

Viewing 15 posts - 1 through 15 (of 36 total)

You must be logged in to reply to this topic. Login to reply