Matt
3/15/2007 9:27:00 PM
I am trying to use the built-in functions to calculate a standard
deviation, and I am getting confused. Here is a runnable example:
create table #t (i int)
insert into #t (i) values (0)
insert into #t (i) values (0)
insert into #t (i) values (14)
insert into #t (i) values (14)
select avg(i), stdev(i), stdevp(i)
from #t
The results I get back are: average 7, stdev 8.082, and stdevp 7.
This seems strange to me. If I calculate the standard deviation by
hand, I get 7, not 8.082. The 8.082 appears to come from taking the
sum of the squares of the differences and dividing by n-1 (rather than
n) and then taking the sqrt. Further testing confirms this. Stdev
uses n-1, stdevp uses n.
But this is not my understanding of how to calculate standard
deviation--you use n, not n-1.
Am I just mistaken about how to calculate standard deviation, or does
SQL Server have an odd definition of stdev vs. stdevp? I'm mostly
curious here, it looks like I can use stdevp to get what I'm looking
for, but I'd like to understand what's going on.
Matt