Re: stddev returns 0 when there is one row

Joe Conway <mail(at)joeconway(dot)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