Calculating Standard Deviation

[Toga]

I need a function that will calculate the standard deviation of a data set out on the server. Does anyone know how to approach this?

 

[johnv20]

Standard dev is a built in function

select @Stddev = ( select STDEV(colA) from Table1 )

To get the median

@median = 
	(
	SELECT x.Timer
	FROM Table1 x, Table1 y
	GROUP BY x.Timer
	HAVING 
   		SUM(CASE WHEN y.Timer <= x.Timer 
      		THEN 1 ELSE 0 END)>=(COUNT(*)+1)/2 AND
   		SUM(CASE WHEN y.Timer >= x.Timer
      		THEN 1 ELSE 0 END)>=(COUNT(*)/2)+1
	)

To get the standard error

select @stderr = 
	(
	select STDEV(Timer)/SQRT(COUNT(*))
	from Table1
	)

Hope this helps

 

[Toga]

Sorry, I didn't leave enough information. I'm trying to do this with a Sybase 11 server and to my surprise, standard deviation isn't a built in function. I need to be able to write the the formula myself in code.

 

[SQLBill]

So, let me make sure I understand you, this is for a SYBASE database server? Not Microsoft SQL Server?

If that's correct, you need to post in a SYBASE forum. This forum is strictly for Microsoft SQL Server.

-SQLBill

Posting advice: FAQ481-4875

 

[vongrunt]

OT issues aside this is solid food for brains (and my first RDBMS ever was Sybase ) so...

Here is useful info about standard deviation. Ignore simplified formula, it generates mismatches for small N.

To calculate stdev, you must aggregate 3 values per group: count of items (in code below cnt), sum of items (sumn) and sum of squares (sumsq). After that formula is simple:

SQRT( ( cnt*sumsq - sumn^2 )/(cnt*(cnt-1)) )

In addition, group must have at least two items. Otherwise stdev must return NULL. All together:

-- sample data. groupcol is column for GROUPing BY
create table blah (groupcol int, n float)
insert into blah select 1, 5.0
insert into blah select 1, 6.0
insert into blah select 1, 8.0
insert into blah select 1, 9.0
insert into blah select 2, 42
insert into blah select 3, 9.0
insert into blah select 3, 100.0

-- check results (SQL2k)
select groupcol, stdev(n) 
from blah
group by groupcol


-- simulated STDEV
select sqrt((cnt*sumsq - sumn*sumn)/(cnt*NULLIF(cnt-1, 0)))
from
(	select groupcol, count(n) as cnt, sum(n) as sumn, sum(n*n) as sumsq
	from blah
	group by groupcol
) X

-- clean up sample data
drop table blah

NULLIF() can be replaced with vanilla CASE... WHEN. Derived tables are supported by ASE 12... dunno for v11.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[Toga]

Yes, I did, but never got an answer. I tried posting it here because the formula, as written in code, should be the same no matter what server it is.

Obviously, calculating standard deviation in SQL server is not an issue (bult in function) but I did not know that was the case.

 

[SQLBill]

Hopefully, vongrunt is able to help you....remember not all SQL is the same. Each version has it's differences in code, which is why I suggested this forum may not be appropriate.

-SQLBill

Posting advice: FAQ481-4875

 

[MichaelRed]

there are also threads in the TEK-TIPS 'archives' showing how to do StdDev in VB(A) using "Jet" Recordsets as well as other venues. So a search on Teh relevalnt terms / keywords. The process is not so difficult, so as a 'last resort' you could just browse ye local bookstore for text on statistics. Any "professional" organization would gladly expense the tome for the work place requirement.





MichaelRed

 

[Toga]

Thanks for everyone's help. I had the formula, but I just didn't know how to approach it wrt getting it into code. With bit's n pieces from everyone, Here's what I came up with. If I dump the data into Excel, it matches exactly. Getting the Specified Criteria placed properly took a little bit of trial and error, and definitely key to getting the right answer.

I still can't believe Sybase didn't have this as a built in fucntion but....whatever. It may not be the best written code but it works! Thanks again everyone!

/*Standard Deviation*/

Declare @MyStDev double precision

select @MyStDev = (SQRT(sum((MyDataColumn-(select
avg(MyDataColumn) From MyDataTbl Where MySpecifiedCriteria))
*
(MyDataColumn -(select MyDataColumn) From MyDataTbl
Where MySpecifiedCriteria)))
/
(select count(*)-1 from MyDataTbl where MySpecifiedCriteria)))
From MyDataTbl
Where MySpecifiedCriteria

 

[Toga]

Ran into a couple issue with my code....essentially floating point overflow.....so I had to modify my code some.

Fortunately, I was aable to resolve the issue by the decimal function within my code to calculate StDev...."(dec(10,2),avg(MyFieldName))"

The next issue I ran into with figuring out how to calculate StDev was blank spaces in the data columns...ie no data. I got around that by adding an abs function to the area of my formula causing the problem.

Anyways.....here's the finished code. I'm not a programmer per se so maybe it's not as good as it could be but the result matches any data set I dump into excel and run the StDev.....where a function actually exist! Still can't believe Sybase wouldn't have included such a function....

select

(SQRT((sum((MyFieldName - (select convert(dec(10,2),avg(MyFieldName))From MyTableNameWhere MyWhereCriteria and abs(MyFieldName)>0))*(MyFieldName - (select convert(dec(10,2),avg(MyFieldName))From MyTableNameWhere MyWhereCriteria and abs(MyFieldName)>0)))) /(select count(MyFieldName)-1 from MyTableNamewhere MyWhereCriteria))) 'StDev'

from MyTableNameWHERE MyWhereCriteria


You all are just too spoiled with SQL server, where such a function already exist!