Re: sum() - unexpected results.

On Mon, 5 Feb 2001, Jeff MacDonald wrote:

> I got some unexpected results from a sum().. just wondering
> if it's a bug of any sort, or if i just should have been expecting it..
>
> SELECT sum(foo) FROM mytable WHERE active = 1;
>
> Now lets say that no rows are active = 1, then this query returns
> 1 blank row..

It is returning NULL, which is what sum() returns when it is asked to sum
0 rows.

> I would have expected either
> 1: a result of zero
> or
> 2: zero rows returned..

If you want a result of zero, use

SELECT coalesce(sum(foo), 0) ...

This is the way SQL-92 says it should be. On page 126 of the document at
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt it says:

b) If AVG, MAX, MIN, or SUM is specified, then

Case:

i) If TXA is empty, then the result is the null value.

(TXA refers to the table constructed from the summed column).
--
Tod McQuillin