Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Generating SubTotals using GROUPING Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, July 14, 2010 3:31 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Friday, February 6, 2015 1:40 AM Points: 125, Visits: 30
 Very useful information especially for the report writers.Thanks,Ameya
Post #952151
 Posted Wednesday, July 14, 2010 10:03 AM
 Valued Member Group: General Forum Members Last Login: Friday, January 29, 2016 2:32 PM Points: 58, Visits: 80
 Nice article, thank you.
Post #952490
 Posted Wednesday, July 14, 2010 10:17 AM
 Forum 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 Group: General Forum Members Last Login: Yesterday @ 1:48 PM Points: 7,765, Visits: 11,376
 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 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. Hugo Kornelis, SQL Server MVPVisit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #952657
 Posted Thursday, July 15, 2010 7:22 AM
 SSCrazy Group: General Forum Members Last Login: Saturday, September 17, 2016 8:01 AM Points: 2,834, Visits: 2,629
 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-Forever Group: General Forum Members Last Login: Today @ 9:38 AM Points: 42,077, Visits: 39,464
 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 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 20You 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." Helpful Links:How to post code problemsHow to post performance problems
Post #954267
 Posted Friday, July 16, 2010 5:32 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 9:38 AM Points: 42,077, Visits: 39,464
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #954268
 Posted Saturday, July 17, 2010 3:36 PM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 1:48 PM Points: 7,765, Visits: 11,376
 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 MVPVisit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #954448
 Posted Sunday, July 18, 2010 11:55 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 9:38 AM Points: 42,077, Visits: 39,464
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #954521
 Posted Monday, July 19, 2010 4:34 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 1:48 PM Points: 7,765, Visits: 11,376
 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 MVPVisit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #954711

 Permissions