Aggregates

Question

Aggregates

Viewing 15 posts - 16 through 30 (of 38 total)

You must be logged in to reply to this topic. Login to reply

sipas Hall of Fame Points: 3828 More actions · Answer 1

Toreador (12/6/2012)
Hugo Kornelis (12/6/2012)
Exactly WHAT is aggregated by the GROUPING_ID function??
That's exactly the conversation I had with myself. Was this a trick question or not? In the end I went with the right answer, though was expecting to be told I was wrong...

BOL says 'Aggregate functions perform a calculation on a set of values and return a single value.', which is not the same as 'aggregating', but I guess they couldn't think of a more appropriate term.

sknox SSChampion Points: 12388 More actions · Answer 2

Hugo Kornelis (12/6/2012)
I picked the right answer, but only after thinking about it for a long time - go with Books Online, or go with common sense. So I am surprised that so many people here consider this to be a simple question. However, the good news is that those people will probably be able to explain me something.
According to the Merriam-Webster English dictionary, the verb "to aggregate" is defiend as "to collect or gather into a mass or whole" (http://www.merriam-webster.com/dictionary/aggregate?show=0&t=1354796918). I checked it, just to make sure, but I am glad that the dictionary definition matches what I consider aggregation to be.
Now I have no problem with CHECKSUM_AGG and STDEVP. Both collect data from multiple rows, and generate a single value from that, be it a checksum value, or the standard deviation of the data collected. No problem. But, and here is the question I hope someone will answer me:
Exactly WHAT is aggregated by the GROUPING_ID function??

I had the same question. Nothing is aggregated by GROUPING_ID. BUT the adjective "aggregate" can mean "formed by the collection of units or particles into a body, mass, or amount " (definition from your link). GROUPING_ID is only relevant to aggregate data by that definition, so in that respect, it is an aggregate function.

venoym SSCarpal Tunnel Points: 4161 More actions · Answer 3

Hugo Kornelis (12/6/2012)
I picked the right answer, but only after thinking about it for a long time - go with Books Online, or go with common sense. So I am surprised that so many people here consider this to be a simple question. However, the good news is that those people will probably be able to explain me something.
According to the Merriam-Webster English dictionary, the verb "to aggregate" is defiend as "to collect or gather into a mass or whole" (http://www.merriam-webster.com/dictionary/aggregate?show=0&t=1354796918). I checked it, just to make sure, but I am glad that the dictionary definition matches what I consider aggregation to be.
Now I have no problem with CHECKSUM_AGG and STDEVP. Both collect data from multiple rows, and generate a single value from that, be it a checksum value, or the standard deviation of the data collected. No problem. But, and here is the question I hope someone will answer me:
Exactly WHAT is aggregated by the GROUPING_ID function??

One could postulate that, somewhat similar to a check sum, the GROUPING_ID takes a set of values and returns a unique value that identifies those values.

However I think the explanation is somewhat more simple, in SQL Server terms it is a built in function that operates over an Expression instead of a single value (or parameter list).

Dave62 SSCertifiable Points: 7459 More actions · Answer 4

ashokkumar.thangavel-639050 (12/6/2012)
Hi
I need a favor here.. Guys, could you please help me on this below scenario,
I have one ssis 2005 package, in this running one sp which will return the result set n stored into one SSIS dataset variable. now the dataset say will have 100 records. what it will do in the current process is, it will fetch one by one in for each loop and get details and generates crystal report. like that it will do it for 100 times.
my requirement is, instead of looping 100 times, i have to reduce the no of times. i need to do it some parellel processing which will reduce the running time of generating 100 reports. please provide me a solution.
Thanks in advance.

Hello and welcome to SQL Server Central. You may get a quicker answer to your question if you post it here: http://ask.sqlservercentral.com/index.html

If you post some of the code you need changed it may help you get a good answer quickly.

Good Luck!

ronmoses@gmail.com SSCarpal Tunnel Points: 4480 More actions · Answer 5

Hugo Kornelis (12/6/2012)
I picked the right answer, but only after thinking about it for a long time - go with Books Online, or go with common sense. So I am surprised that so many people here consider this to be a simple question.

Surely you know there are only four kinds of responses here...

a. "Thanks for the ridiculously simple question that I don't even know why I bothered to answer, since only a moron could possibly get it wrong. Pfft!"

b. "This question was either poorly worded or supported with the wrong BOL reference, therefore I want my points back."

c. [Semi-intelligible ESL reply containing a question of actual substance.]

d. Massively informative HK reply. (just kissin' a little a** here)

ron

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

TomThomson SSC Guru Points: 104773 More actions · Answer 6

It's a nice question.

Hugo raises an interesting point:

Hugo Kornelis (12/6/2012)
I picked the right answer, but only after thinking about it for a long time - go with Books Online, or go with common sense. So I am surprised that so many people here consider this to be a simple question. However, the good news is that those people will probably be able to explain me something.
According to the Merriam-Webster English dictionary, the verb "to aggregate" is defiend as "to collect or gather into a mass or whole" (http://www.merriam-webster.com/dictionary/aggregate?show=0&t=1354796918). I checked it, just to make sure, but I am glad that the dictionary definition matches what I consider aggregation to be.
Now I have no problem with CHECKSUM_AGG and STDEVP. Both collect data from multiple rows, and generate a single value from that, be it a checksum value, or the standard deviation of the data collected. No problem. But, and here is the question I hope someone will answer me:
Exactly WHAT is aggregated by the GROUPING_ID function??

Well, of course, nothing is aggregated by it. I want to agree that this is not a sensible use of the phrase "aggregate function", but when I look hard at the words I see that MS has got it right. The problem is that concentrating on the verb instead of on the adjective(in "aggregate function", "aggregate" is clearly an adjective, not a verb) or on the noun phrase ("aggregate function") leads us astray. I think that GROUPING_ID" just about scrapes in under the bar as a function which is related to a collection of several units, and to the structure of that collection. I don't like it, but the pedant in me tells me I ought to.

Microsoft commit a far worse terminological offense when they call their aggregate XOR function "CHECKSUM_AGG" instead of "XOR_AGG". This aggregate function has nothing to do with CHECKSUM, and including a refernece to the CHECKSUM page in the reference section of the CHECKSUN_AGG page just compounds the lunacy - CHECKSUM, unlike CHECKSUM_AGG, computes a genuine checksum, not a trivial vertical parity. Anyone who has worked on genuine error detection, containment, and recovery will be thoroughly displeased by this misleading terminology. People who use genuine checksums for hashing won't be too happy with it either.

Tom

sestell1 SSChampion Points: 10230 More actions · Answer 7

Toreador (12/6/2012)
Hugo Kornelis (12/6/2012)
Exactly WHAT is aggregated by the GROUPING_ID function??
That's exactly the conversation I had with myself. Was this a trick question or not? In the end I went with the right answer, though was expecting to be told I was wrong...

Yea, I got it wrong thinking that GROUPGING_ID wasn't actually aggregating anything itself but is a function dependent on an aggregation being present. Oh well.

Revenant SSC-Forever Points: 42467 More actions · Answer 8

sestell1 (12/6/2012)
Toreador (12/6/2012)
Hugo Kornelis (12/6/2012)
Exactly WHAT is aggregated by the GROUPING_ID function??
That's exactly the conversation I had with myself. Was this a trick question or not? In the end I went with the right answer, though was expecting to be told I was wrong...
Yea, I got it wrong thinking that GROUPGING_ID wasn't actually aggregating anything itself but is a function dependent on an aggregation being present. Oh well.

That's what QotDs are about. I got it right but only because I consulted BOL.

PChiragS SSCarpal Tunnel Points: 4965 More actions · Answer 9

Nice back to basics question - thanks

If everything seems to be going well, you have obviously overlooked something.

Ron

I believe so..

before putting this question on QotD, was wondering that how are we in basics?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Hugo Kornelis (12/6/2012)
I picked the right answer, but only after thinking about it for a long time - go with Books Online, or go with common sense. So I am surprised that so many people here consider this to be a simple question. However, the good news is that those people will probably be able to explain me something.
According to the Merriam-Webster English dictionary, the verb "to aggregate" is defiend as "to collect or gather into a mass or whole" (http://www.merriam-webster.com/dictionary/aggregate?show=0&t=1354796918). I checked it, just to make sure, but I am glad that the dictionary definition matches what I consider aggregation to be.
Now I have no problem with CHECKSUM_AGG and STDEVP. Both collect data from multiple rows, and generate a single value from that, be it a checksum value, or the standard deviation of the data collected. No problem. But, and here is the question I hope someone will answer me:
Exactly WHAT is aggregated by the GROUPING_ID function??

I found myself wondering the same thing.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

antony-688446 Ten Centuries Points: 1221 More actions · Answer 11

Stewart "Arturius" Campbell (12/5/2012)
Interesting question, thanks.
just bear in mind that GROUPING_ID only came into effect with SQL2008. Those still using 2005 will not have knowledge hereof...

Being pedantic, GROUPING_ID came in with SQL2008 R2, so those using SQL2008 may not have experienced it either...

ken.willekens SSChasing Mays Points: 637 More actions · Answer 12

antony-688446 (12/11/2012)
Stewart "Arturius" Campbell (12/5/2012)
Interesting question, thanks.
just bear in mind that GROUPING_ID only came into effect with SQL2008. Those still using 2005 will not have knowledge hereof...
Being pedantic, GROUPING_ID came in with SQL2008 R2, so those using SQL2008 may not have experienced it either...

What's even worse, my SQL2008 R2 local help doesn't even know about GROUPING_ID. It seems to show the SQL2005 information.

Always check the online help if you're trying to score an easy point to the QOD seems to be the moral of the story 🙂

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 13

antony-688446 (12/11/2012)
Stewart "Arturius" Campbell (12/5/2012)
Interesting question, thanks.
just bear in mind that GROUPING_ID only came into effect with SQL2008. Those still using 2005 will not have knowledge hereof...
Being pedantic, GROUPING_ID came in with SQL2008 R2, so those using SQL2008 may not have experienced it either...

I'm afraid you are mistaken. GROUPING_ID was introduced in SQL 2008, not in SQL 2008 R2.

Link to GROUPING_ID topic in SQL Server 2008 Books Online: http://msdn.microsoft.com/en-us/library/bb510624%28SQL.100%29.aspx

Link to GROUPING_ID topic in SQL Server 2008R2 Books Online: http://msdn.microsoft.com/en-us/library/bb510624%28SQL.105%29.aspx

Link to GROUPING_ID topic in SQL Server 2012 Books Online: http://msdn.microsoft.com/en-us/library/bb510624%28SQL.110%29.aspx

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

ken.willekens SSChasing Mays Points: 637 More actions · Answer 14

Hugo Kornelis (12/12/2012)
antony-688446 (12/11/2012)
Stewart "Arturius" Campbell (12/5/2012)
Interesting question, thanks.
just bear in mind that GROUPING_ID only came into effect with SQL2008. Those still using 2005 will not have knowledge hereof...
Being pedantic, GROUPING_ID came in with SQL2008 R2, so those using SQL2008 may not have experienced it either...
I'm afraid you are mistaken. GROUPING_ID was introduced in SQL 2008, not in SQL 2008 R2.
Link to GROUPING_ID topic in SQL Server 2008 Books Online: http://msdn.microsoft.com/en-us/library/bb510624%28SQL.100%29.aspx
Link to GROUPING_ID topic in SQL Server 2008R2 Books Online: http://msdn.microsoft.com/en-us/library/bb510624%28SQL.105%29.aspx
Link to GROUPING_ID topic in SQL Server 2012 Books Online: http://msdn.microsoft.com/en-us/library/bb510624%28SQL.110%29.aspx

You are right, GROUPING_ID was indeed introduced in SQL2008. But if Antony used Local Help on a SQL2008 and searched for 'Aggregate Functions', the GROUPING_ID function would not show up just as it doesn't show up in SQL2008 R2. Maybe that caused Antony to conclude that the function wasn't in SQL2008 yet.

antony-688446 Ten Centuries Points: 1221 More actions · Answer 15

But if you look here http://msdn.microsoft.com/en-us/library/ms173454(v=sql.100).aspx (aggregate functions in SQL 2008) GROUPING_ID is not listed.

But Here http://msdn.microsoft.com/en-us/library/ms173454(v=sql.105).aspx (SQL 2008R2) its is...

Did MS re-classify GROUPING_ID as an aggregate function between 2008 & R2, or just sort their documentation? Looks like there is more than one version of the truth...