Generating SubTotals using GROUPING

Question

Generating SubTotals using GROUPING

Sunil Chandurkar

Right there with Babe

Points: 791
More actions
July 12, 2010 at 8:37 pm

#221201

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

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

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

R.P.Rozema SSChampion Points: 12307 More actions · Answer 1

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?

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 2

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 More actions · Answer 3

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: 4796 More actions · Answer 4

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 More actions · Answer 5

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 More actions · Answer 6

Here is another good definition of using this style of groups

Eric L Hackett SSC Eights! Points: 978 More actions · Answer 7

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 More actions · Answer 8

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: 281334 More actions · Answer 9

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 More actions · Answer 10

Very useful information especially for the report writers.

Thanks,

Ameya

vpatel1981 SSC Enthusiast Points: 184 More actions · Answer 11

vpatel1981

SSC Enthusiast

Points: 184

July 14, 2010 at 10:03 am

#1192945

Nice article, thank you.

sree1119 SSC Journeyman Points: 99 More actions · Answer 12

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

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 13

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 More actions · Answer 14

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.