December 12, 2012 at 3:31 pm
antony-688446 (12/12/2012)
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...
I think there was a huge overhaul of how the documentation is organized between SQL2008 and SQL2008R2. If you follow the links in my previous post and compare the navigation panes to the left, you'll see that the SQL2008 version includes GROUPING_ID in an alphabetic listing of all keywords, and that SQL2008R2 organizes the keywords in (sort of) logical groups.
December 12, 2012 at 8:20 pm
Hugo Kornelis (12/12/2012)
If you follow the links in my previous post and compare the navigation panes to the left, you'll see that the SQL2008 version includes GROUPING_ID in an alphabetic listing of all keywords, and that SQL2008R2 organizes the keywords inC(sort of) logical groups.
Is "(sort of) logical" the Dutch for "illogical"? 😀
Tom
January 15, 2013 at 1:51 pm
Nice to have repeat the Basic.
January 28, 2013 at 3:59 am
nice question... but bit confusion about STDEVP... how we can use for our developement? any examples....????:-)
Manik
You cannot get to the top by sitting on your bottom.
January 28, 2013 at 4:13 am
manik123 (1/28/2013)
nice question... but bit confusion about STDEVP... how we can use for our developement? any examples....????:-)
If you develop code that has to compute the statistical standard deviation for the population for all values in a table (in other words, the standard deviation over the DISTINCT set of values in a table), you can use this just as any other aggregate function.
If you do not need to compute that standard deviation, you have no need at all for this function.
There is an example included in the Books Online page for STDEVP: http://msdn.microsoft.com/en-us/library/ms176080.aspx.
January 28, 2013 at 12:55 pm
Hugo Kornelis (1/28/2013)
manik123 (1/28/2013)
nice question... but bit confusion about STDEVP... how we can use for our developement? any examples....????:-)If you develop code that has to compute the statistical standard deviation for the population for all values in a table (in other words, the standard deviation over the DISTINCT set of values in a table),
I'm not sure what your parenthetical comment means (or is intended to mean) but if it means what it appears to mean it's wrong.
you can use this just as any other aggregate function.
If you do not need to compute that standard deviation, you have no need at all for this function.
There is an example included in the Books Online page for STDEVP: http://msdn.microsoft.com/en-us/library/ms176080.aspx.
The example uses the default, which is ALL not DISTINCT, which makes the parenthetical comment even harder to understand.
Tom
January 28, 2013 at 2:57 pm
L' Eomot Inversé (1/28/2013)
Hugo Kornelis (1/28/2013)
manik123 (1/28/2013)
nice question... but bit confusion about STDEVP... how we can use for our developement? any examples....????:-)If you develop code that has to compute the statistical standard deviation for the population for all values in a table (in other words, the standard deviation over the DISTINCT set of values in a table),
I'm not sure what your parenthetical comment means (or is intended to mean) but if it means what it appears to mean it's wrong.
you can use this just as any other aggregate function.
If you do not need to compute that standard deviation, you have no need at all for this function.
There is an example included in the Books Online page for STDEVP: http://msdn.microsoft.com/en-us/library/ms176080.aspx.
The example uses the default, which is ALL not DISTINCT, which makes the parenthetical comment even harder to understand.
You're right, I messed that one up. I though I understood the difference between STDEV and STDEVP, but apparently - I don't. And the explanations in Books Online don't help me either.
STDEVP: "the statistical standard deviation for the population for all values"
STDEV: "the statistical standard deviation of all values"
The only difference is the three words "for the population". Probably makes a lot of sense to someone who knows statistics inside out, but I am not that person. *lol*
January 29, 2013 at 5:12 am
Hugo Kornelis (1/28/2013)
I though I understood the difference between STDEV and STDEVP, but apparently - I don't. And the explanations in Books Online don't help me either.STDEVP: "the statistical standard deviation for the population for all values"
STDEV: "the statistical standard deviation of all values"
The only difference is the three words "for the population". Probably makes a lot of sense to someone who knows statistics inside out, but I am not that person. *lol*
The problem is that STDEVP is not actually the standard deviation of anything. It is actually an estimate of the standard deviation of a population for which only the values of a sample are known. BoL expresses it very badly. A better way of describing it would be:
STDEV: the standard deviation of an available sample, which is the standard deviation of a whole population calculated from all its members only if the sample consists of all the members.
STDEVP: an estimate of the standard deviation of a whole population based on data for a sample which is significantly smaller than the whole population.
The relation between the two is easy to express: STDEVP = N*STDEV/(N-1) where N is the number of members in the (available) sample. STDEVP is statistically a significantly better estimate of the standard deviation that STDEV is (in the sense that the statistically expected error in the estimate of the whole population is smaller for STDEVP than for STDEV, if one considers all possible populations and all possible sensibly-sized samples of them), provided the sample is enough smaller than the whole population. Of course in the extreme cases (e.g. sample contains only one member - STDEVP is indeterminate; and sample is whole population - STDEV is accurate and STDEVP is not) STDEV is more useful than STDEVP, but many practical cases are not extreme.
edit:
STDEVP is relevant only to finite populations or to finite samples of infinite populations for which the distribution is not known analytically, not to known continuous distributions or to random variables with known distribution. Of course some random distributions/random variables don't have a standard deviation anyway, and there can be no estimate for what doesn't exist.
Tom
January 29, 2013 at 5:27 am
Thanks for the explanation, Tom! 😉
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply