<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Sunil Chandurkar
»
Generating SubTotals using GROUPING
36 posts, Page 2 of 4
««
1
2
3
4
»
»»
Generating SubTotals using GROUPING
Rate Topic
Display Mode
Topic Options
Author
Message
Ameya- Ameyask
Ameya- Ameyask
Posted Wednesday, July 14, 2010 3:31 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, September 18, 2012 4:53 AM
Points: 123,
Visits: 28
Very useful information especially for the report writers.
Thanks,
Ameya
Post #952151
vpatel-881087
vpatel-881087
Posted Wednesday, July 14, 2010 10:03 AM
Valued Member
Group: General Forum Members
Last Login: Sunday, August 26, 2012 2:24 PM
Points: 56,
Visits: 60
Nice article, thank you.
Post #952490
sree1119
sree1119
Posted Wednesday, July 14, 2010 10:17 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, March 18, 2013 1:45 PM
Points: 3,
Visits: 15
What is the difference between "With Cube" and "Rollup"?
Post #952503
Hugo Kornelis
Hugo Kornelis
Posted Wednesday, July 14, 2010 1:14 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 11:51 AM
Points: 5,232,
Visits: 7,023
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
Daniel Bowlin
Daniel Bowlin
Posted Thursday, July 15, 2010 7:22 AM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 11:53 AM
Points: 2,672,
Visits: 2,416
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
Jeff Moden
Jeff Moden
Posted Friday, July 16, 2010 5:28 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 32,893,
Visits: 26,770
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #954267
Jeff Moden
Jeff Moden
Posted Friday, July 16, 2010 5:32 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 32,893,
Visits: 26,770
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #954268
Hugo Kornelis
Hugo Kornelis
Posted Saturday, July 17, 2010 3:36 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 11:51 AM
Points: 5,232,
Visits: 7,023
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
Jeff Moden
Jeff Moden
Posted Sunday, July 18, 2010 11:55 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 32,893,
Visits: 26,770
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #954521
Hugo Kornelis
Hugo Kornelis
Posted Monday, July 19, 2010 4:34 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 11:51 AM
Points: 5,232,
Visits: 7,023
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 »
36 posts, Page 2 of 4
««
1
2
3
4
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
