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

Joe Conway <mail@joeconway.com> writes:
> Tom Lane wrote:
>> 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?

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

>        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)

Okay, that probably trumps the Oracle precedent, especially seeing that
it seems mathematically sounder.  I'll make the changes.

            regards, tom lane