SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Generating SubTotals using GROUPING


Generating SubTotals using GROUPING

Author
Message
Ameya- Ameyask
Ameya- Ameyask
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 30
Very useful information especially for the report writers.

Thanks,
Ameya
vpatel1981
vpatel1981
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 80
Nice article, thank you.
sree1119
sree1119
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 16
What is the difference between "With Cube" and "Rollup"?
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11108 Visits: 12003
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
Daniel Bowlin
Daniel Bowlin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4136 Visits: 2629
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87418 Visits: 41113
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87418 Visits: 41113
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11108 Visits: 12003
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87418 Visits: 41113
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11108 Visits: 12003
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search