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ökçen 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
>
>
>
>
>
>
>
>
>
>