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