January 27, 2011 at 9:42 pm
Comments posted to this topic are about the item Standard Deviation
January 28, 2011 at 12:08 am
Nice question, really learned something.
The BOL page doesn't even mention this subject.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 28, 2011 at 1:24 am
I think the Explanation is bit confusing.
if you give any numeric value to the function without any from clause it will give NULL.
SELECT STDEV(100)
"The STDEV function uses all of the values to calculate the standard deviation. There must be at least two values in the field or the standard deviation will not be calculated and a NULL is returned."
example in my PackageTable the standard deveation of pa_rates column can find using
select stdev(pa_Rates) From Package
for the above Query i will get some result
but if i cange the Query with some where condition,where i will get only one Row (sample) then the stdevp will results the null value
select stdev(pa_Rates) From Package where pa_PackageID = 1
January 28, 2011 at 1:34 am
sharath.chalamgari (1/28/2011)
but if i cange the Query with some where condition,where i will get only one Row (sample) then the stdevp will results the null value
select stdev(pa_Rates) From Package where pa_PackageID = 1
The WHERE clause is executed first, so you are still calculating the variance over 1 row, hence the NULL.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 28, 2011 at 1:45 am
Koen (da-zero) (1/28/2011)
sharath.chalamgari (1/28/2011)
but if i cange the Query with some where condition,where i will get only one Row (sample) then the stdevp will results the null value
select stdev(pa_Rates) From Package where pa_PackageID = 1
The WHERE clause is executed first, so you are still calculating the variance over 1 row, hence the NULL.
Ya that's what i have explained above.
January 28, 2011 at 1:50 am
sharath.chalamgari (1/28/2011)
Koen (da-zero) (1/28/2011)
sharath.chalamgari (1/28/2011)
but if i cange the Query with some where condition,where i will get only one Row (sample) then the stdevp will results the null value
select stdev(pa_Rates) From Package where pa_PackageID = 1
The WHERE clause is executed first, so you are still calculating the variance over 1 row, hence the NULL.
Ya that's what i have explained above.
Then why is the explanation confusing? I find it pretty clear...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 28, 2011 at 2:01 am
Thanks for introducing new topic. I had not heard about it before
M&M
January 28, 2011 at 2:09 am
I do not agree that the answer is right.
Think it is supposed to be 0.
the standard deviation s (sigma) is the square root of the average value of (X - ยต)2.
Which means s = sqrt(((1-1)^2)/1) = 0
Or in other words std. deviation equals sqrt of (population-avarage) sq / number of population values
Please correct me if im wrong on the formula it has been a while since i used my statistics ๐
January 28, 2011 at 2:34 am
mdv 9731 (1/28/2011)
I do not agree that the answer is right.Think it is supposed to be 0.
the standard deviation s (sigma) is the square root of the average value of (X - ยต)2.
Which means s = sqrt(((1-1)^2)/1) = 0
Or in other words std. deviation equals sqrt of (population-avarage) sq / number of population values
Please correct me if im wrong on the formula it has been a while since i used my statistics ๐
Even if your statistics say it is 0, SQL Server returns NULL, so the answer is still correct.
You can verify it by running the query in SQL Server Management Studio.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 28, 2011 at 2:50 am
I'm not really going to comment on that answer.
Just happy you are not creating any kind of BI og reporting in our company ๐
I might be wrong but i still question the result.
Even if i know that it doesnt make much practical sence to talk deviation on 1 value.
January 28, 2011 at 3:01 am
Koen (da-zero) (1/28/2011)
Then why is the explanation confusing? I find it pretty clear...
The explanation is "The denominator of the variance for a sample population is n - 1, resulting in division by zero."
This suggests it should give an error, and doesn't explain why it results in null instead.
I guessed wrong, but I'm not sure what the question was supposed to prove, other than if you apply a function wrongly then you get a meaningless answer!
January 28, 2011 at 3:17 am
mdv 9731 (1/28/2011)
I'm not really going to comment on that answer.Just happy you are not creating any kind of BI og reporting in our company ๐
I might be wrong but i still question the result.
Even if i know that it doesnt make much practical sence to talk deviation on 1 value.
The question was about the result of the query (ergo: what does SQL Server do with it).
Not what is the theoretical result according to statistics. (however, the explanation might be lacking. I'm (luckily) not a statistician)
But don't worry, if I'll do BI or reporting in your company, I will read the requirements thoroughly ๐
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 28, 2011 at 3:41 am
Koen (da-zero) (1/28/2011)
Then why is the explanation confusing? I find it pretty clear...
It is Confusing because in his Query the parameter 1 does not mean that Number of samples.if you put 100 inplace of 1 still it gives the same null. as per his explanation it is not a divide by zero error
The usage of the Function is wrong in the Query.
see if we can use some thing like below it gives 0
SELECT STDEV(1) from sys.objects
January 28, 2011 at 3:48 am
sharath.chalamgari (1/28/2011)
Koen (da-zero) (1/28/2011)
Then why is the explanation confusing? I find it pretty clear...
It is Confusing because in his Query the parameter 1 does not mean that Number of samples.if you put 100 inplace of 1 still it gives the same null. as per his explanation it is not a divide by zero error
The usage of the Function is wrong in the Query.
see if we can use some thing like below it gives 0
SELECT STDEV(1) from sys.objects
Allright, that makes sense. Thanks for the explanation.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 28, 2011 at 3:57 am
That makes sence.
So its really a matter of SQL Server returning NULL instead of #ERROR not having the correct syntax which obviously means that a FROM clause needs to be included.
So the point proven is more like SQL Server returning a 'value' instead of an error.
And not that it can't calculate ๐ (and is has nothing to do with a zero division)
'cause my point was that std. dev. is 0 if calculated on a single value no matter what it is (even if that makes no sence)
Question good, formula good, Microsoft needs to work on documentation
We're all happy
Cheers
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply