SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Generating SubTotals using GROUPING


Generating SubTotals using GROUPING

Author
Message
Sunil Chandurkar
Sunil Chandurkar
Old Hand
Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)

Group: General Forum Members
Points: 377 Visits: 81
Comments posted to this topic are about the item Generating SubTotals using GROUPING
R.P.Rozema
R.P.Rozema
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2712 Visits: 1716
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
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw


If you don't have time to do it right, when will you have time to do it over?
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18349 Visits: 12426
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Fiacre Lenehan
Fiacre Lenehan
Old Hand
Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)

Group: General Forum Members
Points: 398 Visits: 160
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
Trey Staker
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1588 Visits: 2788
I enjoyed your article. Thank you!

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
Mike Dougherty
Mike Dougherty
Old Hand
Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)

Group: General Forum Members
Points: 310 Visits: 952
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
loki1049
Old Hand
Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)

Group: General Forum Members
Points: 391 Visits: 301
Here is another good definition of using this style of groups
Eric L Hackett
Eric L Hackett
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 211
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
john.arnott
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3508 Visits: 3059
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
SQLRNNR
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64221 Visits: 18570
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search