Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Generating SubTotals using GROUPING Expand / Collapse
Author
Message
Posted Monday, July 12, 2010 8:37 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 6:40 AM
Points: 253, Visits: 79
Comments posted to this topic are about the item Generating SubTotals using GROUPING
Post #951134
Posted Tuesday, July 13, 2010 1:03 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 17, 2014 7:32 AM
Points: 412, Visits: 1,412
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?
Post #951173
Posted Tuesday, July 13, 2010 1:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 6,098, Visits: 8,364
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
Post #951197
Posted Tuesday, July 13, 2010 1:40 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 8:02 AM
Points: 337, Visits: 158
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
Post #951202
Posted Tuesday, July 13, 2010 9:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:43 PM
Points: 1,142, Visits: 2,691
I enjoyed your article. Thank you!

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
Post #951556
Posted Tuesday, July 13, 2010 10:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 22, 2010 8:59 AM
Points: 110, 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?
Post #951657
Posted Tuesday, July 13, 2010 11:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 10, 2012 12:19 PM
Points: 103, Visits: 301
Here is another good definition of using this style of groups
Post #951679
Posted Tuesday, July 13, 2010 11:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 12, 2013 1:44 PM
Points: 69, Visits: 205
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
Post #951695
Posted Tuesday, July 13, 2010 12:34 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
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.
Post #951730
Posted Tuesday, July 13, 2010 10:29 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 8:49 PM
Points: 17,967, Visits: 15,979
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
Post #951996
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse