SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Sunil Chandurkar / Generating SubTotals using GROUPING / Latest Posts

RE: Generating SubTotals using GROUPING

Neha05 — Mon, 24 Dec 2012 12:06:36 GMT

Nice article.

RE: Generating SubTotals using GROUPING

Rob Schripsema — Fri, 16 Nov 2012 11:54:15 GMT

Great article! My brain is finally starting to grasp what ROLLUP does.

RE: Generating SubTotals using GROUPING

cocis48 — Fri, 16 Nov 2012 11:01:05 GMT

good article! and other comments from Hugo and gang. Just a comment, your query #2 is missing the column 'comment' on it.

RE: Generating SubTotals using GROUPING

Peter H — Fri, 16 Nov 2012 09:23:49 GMT

For a prettier output, prefix the product and scheme results with a space (' '+....)Then you don't need to prefix the totals with a 'Z' as they will fall to the end anyway.

RE: Generating SubTotals using GROUPING

Dennis Wagner-347763 — Fri, 16 Nov 2012 05:33:08 GMT

There are two things that I prefer to do in a different way:The first is your use of ISNULL(FieldName, 'ZTotal') -- I prefer to use a case statementCASE GROUPING(FieldName) WHEN 0 THEN FieldName ELSE 'Total' END -- this protects against a NULL value being mistaken for a total The second is your order by using 'ZTotal' to make it go to the bottom -- I prefer to once again use the GROUPING() functionORDER BY GROUPING(FieldName) -- which puts all the non-subtotals at the top since GROUPING() = 0 and the subtotals at the bottom where GROUPING() = 1This also allows you to easily reverse the order and put the totals at the top by adding DESC -- and you don't have to worry about the dreaded 'Zygote' which sorts lower than 'ZTotal'Another suggestion that I saw was using a Cube to get some the rollups to work in both directions -- you can always use a HAVING at the bottom to filter out any groups that you don't want, but you have to be very careful with the GROUP BY order or you'll lose more groupingsExamples:HAVING GROUPING(FieldName) = 0 will not show any sub totals for that groupingHAVING GROUPING(FieldWithID) = GROUPING(FieldWithValue) will not show any extra grouping for fields like StateID and StateValue

RE: Generating SubTotals using GROUPING

Jeff Moden — Tue, 20 Jul 2010 21:13:04 GMT

[quote][b]Hugo Kornelis (7/20/2010)[/b][hr]My final comment in this topic, and then I'll respect R.P.'s request.[quote][b]Jeff Moden (7/19/2010)[/b][hr]Still, the order of run return is, in fact, documented in Books Online...ROLLUP Specifies that in addition to the usual rows provided by GROUP BY, summary rows are introduced into the result set. [font="Arial Black"]Groups are summarized in a hierarchical order[/font], from the lowest level in the group to the highest. [font="Arial Black"]The group hierarchy is determined by the order in which the grouping columns are specified. [/font]Changing the order of the grouping columns can affect the number of rows produced in the result set.[/quote]This quote describes which possible summary rows are and are not introduced in the result set, not the order in which results are returned.(I'm also tempted to point out the use of the term "result set" rather than "recordset" in this quote, but the terminology in BOL as a whole is so often wrong that I'd probably better not go there)Jeff, or anyone else - if you want to continue this debate, then please start a new topic and send me a PM with the link, or include the link in this topic. Without a link, I'll probably never find the new topic.[/quote]It's important that people on this thread realize that WITH ROLLUP and WITH CUBE produce the correct sorted results as stated in BOL. How much plainer can you get than [font="Arial Black"]Groups are summarized in a hierarchical order, from the lowest level in the group to the highest. [/font]

RE: Generating SubTotals using GROUPING

john.arnott — Tue, 20 Jul 2010 12:05:52 GMT

[quote]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.[/quote]I tried to answer this question from the article last week, and realized today that I was wrong in my explanation. I've corrected that post above.

RE: Generating SubTotals using GROUPING

john.arnott — Tue, 20 Jul 2010 11:35:51 GMT

witasj,I see the same contents in @Grouptest as you do. It looks as though the loaded table shown in the article may not be from the final version of the code to build it, but the results with aggregated subtotals do seem to match up to what's shown.

RE: Generating SubTotals using GROUPING

witasj — Tue, 20 Jul 2010 11:05:17 GMT

The result set I get is not the same as the result set posted in the article. Can you repost the code to populate the tables? I had a DBA run it here at my company and he got the same results that I got.First 10 rows:'Product1', 'Scheme1', 1524.15, 124.32, '2010-07-20 16:39:32''Product1', 'Scheme2', 1524.15, 124.32, '2010-07-20 16:39:32''Product1', 'Scheme3', 1524.15, 124.32, '2010-07-20 16:39:32''Product1', 'Scheme4', 1524.15, 124.32, '2010-07-20 16:39:32''Product1', 'Scheme5', 1524.15, 124.32, '2010-07-20 16:39:32''Product1', 'Scheme1', 9144.90, 745.92, '2010-07-20 16:39:32''Product1', 'Scheme2', 4572.45, 372.96, '2010-07-20 16:39:32''Product1', 'Scheme3', 3048.30, 248.64, '2010-07-20 16:39:32''Product1', 'Scheme4', 3048.30, 248.64, '2010-07-20 16:39:32''Product1', 'Scheme5', 3048.30, 248.64, '2010-07-20 16:39:32'

RE: Generating SubTotals using GROUPING

Hugo Kornelis — Tue, 20 Jul 2010 04:16:01 GMT

My final comment in this topic, and then I'll respect R.P.'s request.[quote][b]Jeff Moden (7/19/2010)[/b][hr]Still, the order of run return is, in fact, documented in Books Online...ROLLUP Specifies that in addition to the usual rows provided by GROUP BY, summary rows are introduced into the result set. [font="Arial Black"]Groups are summarized in a hierarchical order[/font], from the lowest level in the group to the highest. [font="Arial Black"]The group hierarchy is determined by the order in which the grouping columns are specified. [/font]Changing the order of the grouping columns can affect the number of rows produced in the result set.[/quote]This quote describes which possible summary rows are and are not introduced in the result set, not the order in which results are returned.(I'm also tempted to point out the use of the term "result set" rather than "recordset" in this quote, but the terminology in BOL as a whole is so often wrong that I'd probably better not go there)Jeff, or anyone else - if you want to continue this debate, then please start a new topic and send me a PM with the link, or include the link in this topic. Without a link, I'll probably never find the new topic.

RE: Generating SubTotals using GROUPING

Jeff Moden — Mon, 19 Jul 2010 16:00:33 GMT

[quote][b]R.P.Rozema (7/19/2010)[/b][hr]Guys, this topic was about the article on the usage of grouping() but you've moved away to wether or not to add a sort clause now. I feel a little guilty for that because I first made a note on the sorting. You've both made your points and since neither can give proof there's no need to keep poluting the topic any more. I personally totally see Hugo's point, so I'll keep on adding the order by clause. Even though I totally respect -if not live by- Jeff's advices normally. Since the sorting is undocumented behavior (unless someone else has a link?) I think it's up to each and everyone to decide for themselves whether or not their solution should rely on it and I don't see any reason not to add the order by clause. If you want to continue this debate, can you please start a separate topic for it?[/quote]The sorting is not undocumented... and that was my point. ;-) And, I am truly humbled by your comment. Thank you.

RE: Generating SubTotals using GROUPING

Jeff Moden — Mon, 19 Jul 2010 15:53:55 GMT

[quote][b]Hugo Kornelis (7/19/2010)[/b][hr][quote][b]Jeff Moden (7/19/2010)[/b][hr]Hugo,The gain is that there is nothing you can naturally order by that will give you the correct original order. ;-)[/quote]Huh?[code="sql"]USE AdventureWorks2008;SELECT TerritoryID, GROUPING(TerritoryID), SalesPersonID, GROUPING(SalesPersonID), SUM(TotalDue) AS SumOfTotalDueFROM Sales.SalesOrderHeaderGROUP BY ROLLUP(TerritoryID, SalesPersonID)ORDER BY GROUPING(TerritoryID), TerritoryID, GROUPING(SalesPersonID), SalesPersonID;[/code][quote]And stop playing the "gotcha back" tricks... can you make the natural sort fail or not?[/quote]Whether or not I can is completely irrelevant. I advise people to include the ORDER BY, because it guarantees correct results. You advise people to omit the ORDER BY clause; in my opinion that places the burden on you to prove that you will never need it. Not even after installing a service pack or upgrading to the next version of SQL Server. Can you guarantee that?Heck, can you even guarantee that this will [b]always[/b] work on the current version of SQL Server? I'm on holiday, with only a simple laptop computer, so my testing capacity is limited. But imagine a SQL Server instance on a computer with 16 cores, with Sales.SalesOrderHeader partitioned by TerritoryID and spread over 16 seperate spindles, and with the amount of data in that table bumped to several billion rows. I would expect (and, in fact, even HOPE) that each core gets to process the data for a single partition. Without the ORDER BY, the data will be returned as soon as it's ready. Why would SQL Server wait for core #1 to finish if core #5 already has some data ready to be returned?Again - if you advise people to rely on undocumented behaviour, the burden is on you to prove that it will work in all cases. And that it will continue to work in the future. If you can't, and you still keep giving the same advise, you are repeating the mistake many people made in the days of SQL Server 6.5 when they told people to omit the ORDER BY after a GROUP BY.[/quote]I humbly stand corrected. That sort works. Well done, Hugo. Still, the order of run return is, in fact, documented in Books Online...ROLLUP Specifies that in addition to the usual rows provided by GROUP BY, summary rows are introduced into the result set. [font="Arial Black"]Groups are summarized in a hierarchical order[/font], from the lowest level in the group to the highest. [font="Arial Black"]The group hierarchy is determined by the order in which the grouping columns are specified. [/font]Changing the order of the grouping columns can affect the number of rows produced in the result set.

RE: Generating SubTotals using GROUPING

john.arnott — Mon, 19 Jul 2010 15:44:45 GMT

Gosh, I hope I don't get my shoes wet by stepping near this, but..... The original script in the article absolutely needs an ORDER BY to properly interleave the two halves of the UNION ALL statement (the detail rows and the aggregated rows). Also, BOL is quite clear in saying that GROUP BY results are not guaranteed to be in any particular order and explicitly prescribes an ORDER BY clause if the order is important. With no direction from MS either way on queries using WITH ROLLUP or ROLLUP(), I'd have to agree that this advice would best be followed in those cases as well.-----edit: Sorry, R.P. I saw your post only after hitting send on mine.

RE: Generating SubTotals using GROUPING

R.P.Rozema — Mon, 19 Jul 2010 15:31:53 GMT

Guys, this topic was about the article on the usage of grouping() but you've moved away to wether or not to add a sort clause now. I feel a little guilty for that because I first made a note on the sorting. You've both made your points and since neither can give proof there's no need to keep poluting the topic any more. I personally totally see Hugo's point, so I'll keep on adding the order by clause. Even though I totally respect -if not live by- Jeff's advices normally. Since the sorting is undocumented behavior (unless someone else has a link?) I think it's up to each and everyone to decide for themselves whether or not their solution should rely on it and I don't see any reason not to add the order by clause. If you want to continue this debate, can you please start a separate topic for it?

RE: Generating SubTotals using GROUPING

Hugo Kornelis — Mon, 19 Jul 2010 14:40:01 GMT

[quote][b]Jeff Moden (7/19/2010)[/b][hr]Hugo,The gain is that there is nothing you can naturally order by that will give you the correct original order. ;-)[/quote]Huh?[code="sql"]USE AdventureWorks2008;SELECT TerritoryID, GROUPING(TerritoryID), SalesPersonID, GROUPING(SalesPersonID), SUM(TotalDue) AS SumOfTotalDueFROM Sales.SalesOrderHeaderGROUP BY ROLLUP(TerritoryID, SalesPersonID)ORDER BY GROUPING(TerritoryID), TerritoryID, GROUPING(SalesPersonID), SalesPersonID;[/code][quote]And stop playing the "gotcha back" tricks... can you make the natural sort fail or not?[/quote]Whether or not I can is completely irrelevant. I advise people to include the ORDER BY, because it guarantees correct results. You advise people to omit the ORDER BY clause; in my opinion that places the burden on you to prove that you will never need it. Not even after installing a service pack or upgrading to the next version of SQL Server. Can you guarantee that?Heck, can you even guarantee that this will [b]always[/b] work on the current version of SQL Server? I'm on holiday, with only a simple laptop computer, so my testing capacity is limited. But imagine a SQL Server instance on a computer with 16 cores, with Sales.SalesOrderHeader partitioned by TerritoryID and spread over 16 seperate spindles, and with the amount of data in that table bumped to several billion rows. I would expect (and, in fact, even HOPE) that each core gets to process the data for a single partition. Without the ORDER BY, the data will be returned as soon as it's ready. Why would SQL Server wait for core #1 to finish if core #5 already has some data ready to be returned?Again - if you advise people to rely on undocumented behaviour, the burden is on you to prove that it will work in all cases. And that it will continue to work in the future. If you can't, and you still keep giving the same advise, you are repeating the mistake many people made in the days of SQL Server 6.5 when they told people to omit the ORDER BY after a GROUP BY.

RE: Generating SubTotals using GROUPING

Jeff Moden — Mon, 19 Jul 2010 06:34:21 GMT

Hugo,The gain is that there is nothing you can naturally order by that will give you the correct original order. ;-) And stop playing the "gotcha back" tricks... can you make the natural sort fail or not?

RE: Generating SubTotals using GROUPING

Hugo Kornelis — Mon, 19 Jul 2010 04:34:16 GMT

[quote][b]Jeff Moden (7/18/2010)[/b][hr][quote][b]Hugo Kornelis (7/17/2010)[/b][hr][quote][b]Jeff Moden (7/16/2010)[/b][hr]Nice job, Sunil. I especially liked the fact that you compared what some folks go through with multiple queries (in one form or another) and the simplicity of WITH ROLLUP.As a side bar, you shouldn't use ORDER BY with either ROLLUP or CUBE... it's one of the very few places in SQL Server where the order is pretty much guaranteed to be correct and in the order expected.[/quote]I have to contradict. There is only one way to guarantee order in SQL Server, and that is by specifying ORDER BY. Everything else depends.And even if it were not necessary, I'd still include it. It won't hurt performance (except maybe a few microseconds parse time), and it hardens my code against future changes to the engine.[/quote]Not a problem... show me just one example where it fails on its own as a final output.[/quote]I'm on holiday, so I won't even try to find an example. Note that it doesn't matter - read what I wrote: "it hardens my code against future changes to the engine".Can you show me an example in SQL Server 6.5 where GROUP BY without ORDER BY will not produce ordered results? And yet, many people who relied on this behaviour werefound they had shot themselves in the foot when upgrading to SQL Server 7.0.Can you show me an example in SQL Server 2005 where a SELECT without ORDER BY on a VIEW with TOP 100 PERCENT and ORDER BY will not produce ordered results? And yet, once again, many people found that relying on this behaviour caused a bullet-shaped hole in their foot.But let's reverse the challenge. Can you show me an example where a ROLLUP with ORDER BY performs worse than one without ORDER BY? And if you can't, why would you insist on removing the ORDER BY? What is the gain?

RE: Generating SubTotals using GROUPING

Jeff Moden — Sun, 18 Jul 2010 11:55:24 GMT

[quote][b]Hugo Kornelis (7/17/2010)[/b][hr][quote][b]Jeff Moden (7/16/2010)[/b][hr]Nice job, Sunil. I especially liked the fact that you compared what some folks go through with multiple queries (in one form or another) and the simplicity of WITH ROLLUP.As a side bar, you shouldn't use ORDER BY with either ROLLUP or CUBE... it's one of the very few places in SQL Server where the order is pretty much guaranteed to be correct and in the order expected.[/quote]I have to contradict. There is only one way to guarantee order in SQL Server, and that is by specifying ORDER BY. Everything else depends.And even if it were not necessary, I'd still include it. It won't hurt performance (except maybe a few microseconds parse time), and it hardens my code against future changes to the engine.[/quote]Not a problem... show me just one example where it fails on its own as a final output.

RE: Generating SubTotals using GROUPING

Hugo Kornelis — Sat, 17 Jul 2010 15:36:01 GMT

[quote][b]Jeff Moden (7/16/2010)[/b][hr]Nice job, Sunil. I especially liked the fact that you compared what some folks go through with multiple queries (in one form or another) and the simplicity of WITH ROLLUP.As a side bar, you shouldn't use ORDER BY with either ROLLUP or CUBE... it's one of the very few places in SQL Server where the order is pretty much guaranteed to be correct and in the order expected.[/quote]I have to contradict. There is only one way to guarantee order in SQL Server, and that is by specifying ORDER BY. Everything else depends.And even if it were not necessary, I'd still include it. It won't hurt performance (except maybe a few microseconds parse time), and it hardens my code against future changes to the engine.

RE: Generating SubTotals using GROUPING

Jeff Moden — Fri, 16 Jul 2010 17:32:08 GMT

Nice job, Sunil. I especially liked the fact that you compared what some folks go through with multiple queries (in one form or another) and the simplicity of WITH ROLLUP.As a side bar, you shouldn't use ORDER BY with either ROLLUP or CUBE... it's one of the very few places in SQL Server where the order is pretty much guaranteed to be correct and in the order expected.

RE: Generating SubTotals using GROUPING

Jeff Moden — Fri, 16 Jul 2010 17:28:59 GMT

[quote][b]Eric L Hackett (7/13/2010)[/b][hr]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 5Meter1 12/31/09 Dec 2009 10Meter1 DecTotal 2009 15Meter1 2009Total 15Meter2 1/1/10 Jan 2010 8Meter2 1/2/10 Jan 2010 12Meter2 JanTotal 2010 20Meter2 2010Total 20[/quote]You should probably start a regular forum post but the following article should do it for you...[url]http://www.sqlservercentral.com/articles/Crosstab/65048/[/url]

RE: Generating SubTotals using GROUPING

Daniel Bowlin — Thu, 15 Jul 2010 07:22:26 GMT

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.

RE: Generating SubTotals using GROUPING

Hugo Kornelis — Wed, 14 Jul 2010 13:14:45 GMT

[quote][b]sree1119 (7/14/2010)[/b][hr]What is the difference between "With Cube" and "Rollup"?[/quote]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 totalIf 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.

RE: Generating SubTotals using GROUPING

sree1119 — Wed, 14 Jul 2010 10:17:38 GMT

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

RE: Generating SubTotals using GROUPING

vpatel-881087 — Wed, 14 Jul 2010 10:03:12 GMT

Nice article, thank you.

RE: Generating SubTotals using GROUPING

Ameya- Ameyask — Wed, 14 Jul 2010 03:31:09 GMT

Very useful information especially for the report writers.Thanks,Ameya

RE: Generating SubTotals using GROUPING

SQLRNNR — Tue, 13 Jul 2010 22:29:00 GMT

Thanks for the article.

RE: Generating SubTotals using GROUPING

john.arnott — Tue, 13 Jul 2010 12:34:44 GMT

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.[code="sql"]SELECT Product_Name,Scheme_Name,Invest_Amount, broker_commission,-1 AS GrandTotal,-1 as ProductTotal --<=== Added thisFROM @GroupTest UNION ALLSELECT 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 thisFROM @GroupTestGROUP BY Product_Name,Scheme_NameWITH ROLLUPORDER BY Product_Name,Scheme_Name, GrandTotal[/code]From the article:[quote]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.[/quote] 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." [b] This is wrong. Let me strike it out.....[/b][strike]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.[/strike]

RE: Generating SubTotals using GROUPING

Eric L Hackett — Tue, 13 Jul 2010 11:32:57 GMT

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 5Meter1 12/31/09 Dec 2009 10Meter1 DecTotal 2009 15Meter1 2009Total 15Meter2 1/1/10 Jan 2010 8Meter2 1/2/10 Jan 2010 12Meter2 JanTotal 2010 20Meter2 2010Total 20

RE: Generating SubTotals using GROUPING

loki1049 — Tue, 13 Jul 2010 11:08:06 GMT

[url=http://odetocode.com/code/85.aspx]Here[/url] is another good definition of using this style of groups

RE: Generating SubTotals using GROUPING

Mike Dougherty — Tue, 13 Jul 2010 10:39:06 GMT

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?

RE: Generating SubTotals using GROUPING

Trey Staker — Tue, 13 Jul 2010 09:14:27 GMT

I enjoyed your article. Thank you!

RE: Generating SubTotals using GROUPING

Fiacre Lenehan — Tue, 13 Jul 2010 01:40:25 GMT

With Rollup can be replaced with Rollup [code="sql"]SELECT Product_Name,Scheme_Name,Invest_Amount, broker_commission,-1 AS IsSchemeTotal,-1 as IsGrandTotalFROM @GroupTestUnion ALLselect isnull(Product_Name, 'ZProductTotal'),isnull(Scheme_Name,'ZTotal'), sum(Invest_Amount),SUM(Broker_Commission),GROUPING(Scheme_Name) AS IsSchemeTotal,GROUPING(Product_Name) AS IsGrandTotalfrom @grouptestgroup by rollup (Product_Name,Scheme_Name)ORDER BY Product_Name,Scheme_Name,IsSchemeTotal,IsGrandTotal[/code]To give the same results

RE: Generating SubTotals using GROUPING

Hugo Kornelis — Tue, 13 Jul 2010 01:36:00 GMT

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.

RE: Generating SubTotals using GROUPING

R.P.Rozema — Tue, 13 Jul 2010 01:03:26 GMT

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.[code="sql"]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 @GroupTestGROUP BY Product_Name,Scheme_NameWITH ROLLUPORDER BY Product_Name,Scheme_Name, IsSchemeTotal, IsGrandTotal[/code]An excerpt of the output is now:Product_name Scheme_Name Invest_Amount Broker_Commission [b]IsSchemeTotal IsGrandTotal[/b]...Product9 Scheme5 480107.25 39160.80 [b]0 0[/b]Product9 ZTotal 5304042.00 432633.60 [b]1 0[/b]ZProductTotal ZTotal 24747623.55 2018583.84 [b]1 1[/b]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 [code="sql"]ORDER BY IsGrandTotal, Product_Name, IsSchemeTotal, Scheme_Name[/code], you'l always get the 'normal' rows before the subtotals before the grand totals.

Generating SubTotals using GROUPING

Sunil Chandurkar — Mon, 12 Jul 2010 20:37:13 GMT

Comments posted to this topic are about the item [B]Generating SubTotals using GROUPING[/B]