Re: stddev returns 0 when there is one row

Manfred Koizar wrote:
> On Sat, 19 Apr 2003 11:11:21 -0500, Bruno Wolff III <bruno(at)wolff(dot)to>
> wrote:
>
>>stddev returns 0 when the number of rows is one. I would have expected null
>>to be returned in this case as the standard deviation is undefined when
>>there is one row.
>
> As far as a little googling can tell, there a two kinds of standard
> deviation: Depending on whether you are calculating the standard
> deviation of the *whole population* or of a *sample* you divide by N
> or by (N - 1), respectively, before you take the square root.
>
> I'm not an expert, what I say here is from
> http://www.beyondtechnology.com/tips016.shtml. Other web pages seem
> to say the same. OpenOffice.org Calc has two flavours of standard
> deviation, too.
>

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.

Joe