Generating SubTotals using GROUPING

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

  • 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

    ...

    Product9Scheme5480107.2539160.8000

    Product9ZTotal5304042.00432633.6010

    ZProductTotalZTotal24747623.552018583.8411

    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?

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

  • 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

  • I enjoyed your article. Thank you!

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

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

  • Here is another good definition of using this style of groups

  • 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

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

  • 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

  • Very useful information especially for the report writers.

    Thanks,

    Ameya

  • Nice article, thank you.

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

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

  • 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