<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by jkelly
»
Standard Deviation
29 posts, Page 2 of 3
««
1
2
3
»»
Standard Deviation
Rate Topic
Display Mode
Topic Options
Author
Message
Toreador
Toreador
Posted Friday, January 28, 2011 3:01 AM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 9:52 AM
Points: 1,356,
Visits: 4,761
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!
Post #1055181
Koen Verbeeck
Koen Verbeeck
Posted Friday, January 28, 2011 3:17 AM
SSCrazy Eights
Group: General Forum Members
Last Login: 2 days ago @ 6:54 AM
Points: 9,364,
Visits: 6,462
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?
Member of
LinkedIn
. My blog at
LessThanDot
.
MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1055188
sharath.chalamgari
sharath.chalamgari
Posted Friday, January 28, 2011 3:41 AM
Ten Centuries
Group: General Forum Members
Last Login: Monday, September 17, 2012 7:30 AM
Points: 1,038,
Visits: 679
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
Post #1055195
Koen Verbeeck
Koen Verbeeck
Posted Friday, January 28, 2011 3:48 AM
SSCrazy Eights
Group: General Forum Members
Last Login: 2 days ago @ 6:54 AM
Points: 9,364,
Visits: 6,462
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?
Member of
LinkedIn
. My blog at
LessThanDot
.
MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1055198
mdv 9731
mdv 9731
Posted Friday, January 28, 2011 3:57 AM
Grasshopper
Group: General Forum Members
Last Login: Friday, February 22, 2013 3:35 AM
Points: 20,
Visits: 92
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
Post #1055203
valeryk2000
valeryk2000
Posted Friday, January 28, 2011 6:13 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, April 30, 2013 12:01 PM
Points: 117,
Visits: 225
Division by zero should be ERROR, not null. How come?
Post #1055261
Barry McConnell
Barry McConnell
Posted Friday, January 28, 2011 7:24 AM
SSC-Addicted
Group: General Forum Members
Last Login: Tuesday, June 07, 2011 7:20 AM
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.
Post #1055319
UMG Developer
UMG Developer
Posted Friday, January 28, 2011 10:11 AM
SSCrazy
Group: General Forum Members
Last Login: Monday, May 13, 2013 11:21 AM
Points: 2,163,
Visits: 2,148
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.
Post #1055453
wolfkillj
wolfkillj
Posted Friday, January 28, 2011 10:12 AM
Right there with Babe
Group: General Forum Members
Last Login: Yesterday @ 3:57 PM
Points: 721,
Visits: 1,366
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!
Post #1055455
UMG Developer
UMG Developer
Posted Friday, January 28, 2011 10:24 AM
SSCrazy
Group: General Forum Members
Last Login: Monday, May 13, 2013 11:21 AM
Points: 2,163,
Visits: 2,148
Thanks for the explanation wolfkillj! That makes total sense now.
Post #1055464
« Prev Topic
|
Next Topic »
29 posts, Page 2 of 3
««
1
2
3
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
