Hi Mario,
sorry about mismatch stddev formula,
i was take it from one of engineering edu sites, and there was no
time to test it.

Here is another calculation of STDDEV without AVG operation,
if performance or optimisation or system load important for you too.

"http://listweb.bilkent.edu.tr/turkstat/2004/Feb/att-
0001/standart_sapma_formulleri.doc"

extra a few seconds may not be so importand on
stanalone/local/embedded
systems but they are escaped seconds from other users respons times
in multiuser environments.

Regards,

Ali

--- In firebird-support@yahoogroups.com, "Mario" <mario1fb@y...>
wrote:

> Hi Ali,
> I have taken the UDF code below exactly as written from the IB UDF
> library (see relevant LANGREF.PDF), and it works well with my

STDDEV SP.

>
> > > DECLARE EXTERNAL FUNCTION SQRT
> > > DOUBLE PRECISION
> > > RETURNS DOUBLE PRECISION BY VALUE
> > > ENTRY_POINT 'IB_UDF_sqrt' MODULE_NAME 'ib_udf';
>
> > did you send DOUBLE PRECISION to sqrt?
> > REAL is NOT DOUBLE PRECISION(8 bytes float),
> > it is single precision (4 bytes float).
>
> I have tested your suggested "sql friendly new formula (one

pass).":

> select sqrt( count(x)*sum(x*x)- sum(x)*sum(x) ) / count(x)
> from mytable
> where mydate>=?
>
> and unfortunately, it produces different results than the original
> STDDEV formula (both with division by count(x) and by count(x)-1).
>
> Since I also need to obtain the avg(x), the double select

statement in

> SP works well for me.
> Thanks again for all your suggestions.
> Mario
>
> >
> >
> > > select avg(xindex), count(xindex) from xtable
> > > where xDate > :FirstDate
> > > into :avgindex,:countindex;
> > > if (:countindex > 1) then
> > > select (sum((xindex - :avgindex)*(xindex - :avgindex)) /
> > > (:countindex - 1)) from xtable
> > > where xDate > :FirstDate
> > > into :stddev;
> >
> > be careful here please,
> > you are doing 2 selects.
> > if your db on heavy load(too much users and inserts), then there

may

> > be some commits between these 2 selects, and you may get some
> > incorrect stddev result.
> > (if your transaction is not in snapshot isolation level)
> > use snaphot transaction for this operation or
> > use single select operation as i write in previous mail or
> > eliminate the usage of new records via ID filtering or
> > etc etc..
> >
> > Regards.
> > Ali