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 Wednesday, July 14, 2010 3:31 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 9:52 PM
Points: 125, Visits: 29
Very useful information especially for the report writers.

Thanks,
Ameya
Post #952151
Posted Wednesday, July 14, 2010 10:03 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, August 26, 2012 2:24 PM
Points: 56, Visits: 60
Nice article, thank you.
Post #952490
Posted Wednesday, July 14, 2010 10:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 26, 2013 9:38 AM
Points: 3, Visits: 16
What is the difference between "With Cube" and "Rollup"?
Post #952503
Posted Wednesday, July 14, 2010 1:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:22 PM
Points: 5,925, Visits: 8,174
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #952657
Posted Thursday, July 15, 2010 7:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:41 AM
Points: 2,818, Visits: 2,553
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.
Post #953110
Posted Friday, July 16, 2010 5:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:30 PM
Points: 36,766, Visits: 31,222
Eric L Hackett (7/13/2010)
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


You should probably start a regular forum post but the following article should do it for you...
http://www.sqlservercentral.com/articles/Crosstab/65048/


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #954267
Posted Friday, July 16, 2010 5:32 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:30 PM
Points: 36,766, Visits: 31,222
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.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #954268
Posted Saturday, July 17, 2010 3:36 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:22 PM
Points: 5,925, Visits: 8,174
Jeff Moden (7/16/2010)
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.


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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #954448
Posted Sunday, July 18, 2010 11:55 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:30 PM
Points: 36,766, Visits: 31,222
Hugo Kornelis (7/17/2010)
Jeff Moden (7/16/2010)
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.


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.


Not a problem... show me just one example where it fails on its own as a final output.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #954521
Posted Monday, July 19, 2010 4:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:22 PM
Points: 5,925, Visits: 8,174
Jeff Moden (7/18/2010)
Hugo Kornelis (7/17/2010)
Jeff Moden (7/16/2010)
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.


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.


Not a problem... show me just one example where it fails on its own as a final output.


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?



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #954711
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse