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


Standard Deviation


Standard Deviation

Author
Message
Toreador
Toreador
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2769 Visits: 8084
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!
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27317 Visits: 13268
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 ;-)


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
sharath.chalamgari
sharath.chalamgari
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1476 Visits: 798
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


Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27317 Visits: 13268
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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
mdv 9731
mdv 9731
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 126
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
valeryk2000
valeryk2000
SSC Veteran
SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)

Group: General Forum Members
Points: 291 Visits: 412
Division by zero should be ERROR, not null. How come?
Barry McConnell
Barry McConnell
SSC-Addicted
SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)

Group: General Forum Members
Points: 432 Visits: 83
Right answer, wrong explanation. The stdev function is simply programmed to return null when the sample size is less than 2. It has nothing to do with division by zero. Division by zero is an invalid calculation not an unknown result. If it were allowed to perform the zero division the proper response of the function should have been to throw an error; instead they trapped the invalid condition and returned a NULL. I would also argue that this is an inaccurate result since it ignores the fact that an invalid data set is being used without returning the appropriate error.
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2680 Visits: 2204
Thanks for the question, it is good to know that SQL Server decides to return NULL for the standard deviation for a data value, though I don't think it is alone.
wolfkillj
wolfkillj
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 2582
What's going on here is that the STDEV() function in T-SQL returns the sample standard deviation (which for a single value will be undefined, hence the programmatically dictated NULL result) rather than the population standard deviation (which for a single value will be 0). BOL does not clearly state this, which apparently causes some confusion. Another T-SQL function, STDEVP(), returns the population standard deviation. Developers using statistical functions in T-SQL definitely should be aware of this!

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2680 Visits: 2204
Thanks for the explanation wolfkillj! That makes total sense now.
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