Subject | Re: [firebird-support] Re: Aggregate UDF? |
---|---|

Author | Michael Ludwig |

Post date | 2010-06-25T17:24:39Z |

Svein Erling schrieb am 25.06.2010 um 13:50 (-0000):

but I'm getting different results for your stored procedure and your

CTE.

CREATE TABLE T( A INTEGER );

Insert 0, 1, 2, 3 and so on through 10. The esult in both cases is

3.162277660168380. So far, so good. Now add 30 and commit.

INSERT INTO T VALUES (30); COMMIT;

WITH CTE(MySum) AS (

SELECT

AVG( a * a )

- ( AVG( a ) * AVG( a ) )

FROM t

)

SELECT SQRT(MySum) FROM CTE;

SQRT

=======================

7.615773105863909

select * from p_stddev('a', 't');

STDDEV

=======================

7.544313531837517

--

Michael Ludwig

> Nothing is like trying, and to me, PenWins suggestions seems OK -Not doubting or questioning the mathematical soundness of the formula,

> except that he forgot the square root. At least, the following

> produced a sensible result for my first (and only) test:

>

> with CTE(MySum) as

> (select avg(MyField*MyField)-(avg(MyField)*avg(MyField))

> from TEST_TABLE)

> select sqrt(MySum)

> from CTE

but I'm getting different results for your stored procedure and your

CTE.

CREATE TABLE T( A INTEGER );

Insert 0, 1, 2, 3 and so on through 10. The esult in both cases is

3.162277660168380. So far, so good. Now add 30 and commit.

INSERT INTO T VALUES (30); COMMIT;

WITH CTE(MySum) AS (

SELECT

AVG( a * a )

- ( AVG( a ) * AVG( a ) )

FROM t

)

SELECT SQRT(MySum) FROM CTE;

SQRT

=======================

7.615773105863909

select * from p_stddev('a', 't');

STDDEV

=======================

7.544313531837517

--

Michael Ludwig