Re: stddev returns 0 when there is one row - Mailing list pgsql-general

On Sat, Apr 19, 2003 at 12:50:01 -0700,
  Joe Conway <mail@joeconway.com> wrote:
>
> There doesn't seem to be anything in SQL99 about this, but in SQL200x I
> found in section 4.16.3:
>
> — If STDDEV_POP is specified, then the population standard deviation of
> <value expression>
> evaluated for each row remaining in the group, defined as the square
> root of the population
> variance.
> — If STDDEV_SAMP is specified, then the sample standard deviation of
> <value expression> evaluated
> for each row remaining in the group, defined as the square root of the
> sample variance.
>
> So I'd take it that PostgreSQL's STDDEV implements STDDEV_POP.

Postgres's stddev is n-1 weighted. It specifically checks for n=0
(for which it returns null) and n=1 for which it returns 0.
From a mathematical standpoint returning 0 is odd, since the value
is undefined.