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

Tom Lane wrote:
[PostgreSQL's STDDEV is a sample standard deviation, *not* a population
standrad deviation]
> 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?
>

I guess that's what I get for jumping to conclusions ;-0

The spec does have specific guidance in section
10.9 <aggregate function>:

j) STDDEV_SAMP(X) is equivalent to SQRT(VAR_SAMP(X)).

- and -

viii) If VAR_POP or VAR_SAMP is specified, then let S1 be the sum of
       values in the column of TXA, and S2 be the sum of the squares of
       the values in the column of TXA.
       1) If VAR_POP is specified, then the result is (S2-S1*S1/N)/N.
       2) If VAR_SAMP is specified, then:
          A) If N is 1 (one), then the result is the null value.
          B) Otherwise, the result is (S2-S1*S1/N)/(N-1)

Joe