Hi,

if i remember correctly, STDDEV is:

if(count(x)>1)
sqrt ( sum((x-avg(x))*(x-avg(x))) / (count(x)-1) )

Regards.

Ali

--- In firebird-support@yahoogroups.com, Adomas Urbanavicius
<adomas@i...> wrote:

> I wonder if it is not possible to write in one select:
> (i dont remember exacty what is stddev, but if it is

SQRT

> (SUM( (x - mean(x) / N))
> simple idea :
> select
> sqrt ( sum (x - avg(x))/count(x) )
> from tb
>
> As nested agregations are not allowed,
> in sql realization would be something like
>
> select
> sqrt
> (
> sum
> (x - (select avg(x) from tb)/
> (select count(x) from tb)
> )
> )
> from tb
> Of course , this will be definetely slower than sample before in

sp, so

> final idea woul be :
> create sp ....
> select avg(x),count(x) from tb into avg_x,count_x
> select sqrt(sum (x - avg_x)/count_x) from tb into res;
>
>
> Ali G?n wrote:
>
> >Hey Mario,
> >why are you so pessimist? what is to be a programmer?
> > to be a drag-dropper? or visual screen graphic designer?
> >
> >if writing a STDDEV function using a procedure very difficult for
> >you, then how may you write more complex programs?
> >
> >what is STDDEViation?
> >
> >n=total=0
> >sampleavg = AVG(myfiled) -- use coalasce if there is null values
> >
> >for
> >select samplevalue from mytable into :myvalue
> >do
> >begin
> > n= n +1
> > total = total + (myvalue - sampleavg)*(myvalue - sampleavg);
> >end
> >stddev = sqrrootfunctionofUDF(total / (n-1))
> >
> >if there is no sqrroot function in UDF, (i never need,
> >so i dont now if exists) you can write one yourself using newton
> >rapson method for example...
> >
> >Regards.
> >
> >Ali
> >
> >--- In firebird-support@yahoogroups.com, "mario1fb"

<mario1fb@y...>

> >wrote:
> >
> >
> >>Thank you Philip for clarification. Most appreciated. At least I
> >>
> >>
> >know
> >
> >
> >>now that STDDEV cannot be calculated using the Interbase SQL

query.

> >>
> >>BTW, 'in the old DOS days' this task could be done with just a
> >>
> >>
> >single
> >
> >
> >>line of code, e.g. in FoxPro database:
> >>CALC CNT(), AVG(INDEX), STD(INDEX) TO aCNT, aAVG, aSTD
> >>
> >>and the result was calculated in a split of a second, no indexes
> >>required ...
> >>It appears that I will now have to write the whole complex
> >>
> >>
> >procedure
> >
> >
> >>in Delphi to extract the data from the table, place them in the
> >>
> >>
> >memory
> >
> >
> >>array, and finally use the Delphi built in functions to

calculate

> >>
> >>
> >the
> >
> >
> >>STDDEV, in order to achieve the same result.
> >>
> >>Unless, as Philip suggested, someone has done this better
> >>
> >>
> >already ?...
> >
> >
> >>Any suggestions would be most welcome.
> >>
> >>Regards,
> >>Mario
> >>
> >>
> >>--- In firebird-support@yahoogroups.com, "unordained"
> >><unordained_00@c...> wrote:
> >>
> >>
> >>>Since nobody else is replying to this ...
> >>>
> >>>a) No, it's not an available aggregate function, nor is the MEAN
> >>>
> >>>
> >()
> >
> >
> >>you would need in order to
> >>
> >>
> >>>calculate the STDDEV manually.
> >>>
> >>>b) No, you can't add it as a user-defined-function (UDF) because
> >>>
> >>>
> >>Interbase and Firebird don't
> >>
> >>
> >>>expose an API by which your own functions could get multiple
> >>>
> >>>
> >rows of
> >
> >
> >>data. (I learned this when I
> >>
> >>
> >>>asked about a product-aggregate instead of an additive-
> >>>
> >>>
> >aggregate.)
> >
> >
> >>>So if it's not built-in, and you can't add it as a UDF, then

that

> >>>
> >>>
> >>pretty much leaves you writing a
> >>
> >>
> >>>stored procedure (SP). I'm terrible at writing SP's, so you'll
> >>>
> >>>
> >want
> >
> >
> >>to get help from someone else.
> >>
> >>
> >>>It's possible someone already has one written. A very quick
> >>>
> >>>
> >google
> >
> >
> >>search isn't turning one up.
> >>
> >>
> >>>-Philip
> >>>
> >>>---------- Original Message -----------
> >>>From: "mario1fb" <mario1fb@y...>
> >>>
> >>>
> >>>
> >>>>Interbase alows for quick calculation of Sum, Average, etc.
> >>>>
> >>>>
> >using
> >
> >
> >>>>simple select statement:
> >>>>e.g.
> >>>>select AVG(INDEX), SUM(INDEX) from DATATABLE
> >>>>
> >>>>Is there a way of calculating Standard Deviation (STDDEV) using
> >>>>similar technique ?
> >>>>How about Firebird capabilities in this area ?
> >>>>
> >>>>Mario
> >>>>
> >>>>
> >>>------- End of Original Message -------
> >>>
> >>>
> >
> >
> >
> >
> >++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> >Visit http://firebird.sourceforge.net and click the Resources item
> >on the main (top) menu. Try Knowledgebase and FAQ links !
> >
> >Also search the knowledgebases at http://www.ibphoenix.com
> >
> >++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> >Yahoo! Groups Links
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >