Re: stddev returns 0 when there is one row

Joe Conway <mail@joeconway.com> writes:
> So I'd take it that PostgreSQL's STDDEV implements STDDEV_POP.

No, we implement the sample standard deviation, as stated in the docs:
http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/functions-aggregate.html

The code is pretty straightforward, at least in the float8 case:

    /* We define STDDEV of no values to be NULL, of 1 value to be 0 */
    if (N == 0.0)
        PG_RETURN_NULL();

    if (N <= 1.0)
        PG_RETURN_FLOAT8(0.0);

    numerator = N * sumX2 - sumX * sumX;

    /* Watch out for roundoff error producing a negative numerator */
    if (numerator <= 0.0)
        PG_RETURN_FLOAT8(0.0);

    PG_RETURN_FLOAT8(sqrt(numerator / (N * (N - 1.0))));

I don't have a real strong feeling about whether we should change the
behavior at N=1 or not.  Does the SQL200x spec provide any guidance?

            regards, tom lane