Re: sum() - unexpected results.

Jeff MacDonald <jeff(at)hub(dot)org> writes:
> 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..

SUM of no rows returns NULL, per SQL92 section 6.5 general rule 2:

Case:

a) If the <general set function> COUNT is specified, then the
result is the cardinality of TXA.

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

Case:

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

ii) If AVG is specified, then the result is the average of the
values in TXA.

iii) If MAX or MIN is specified, then the result is respec-
tively the maximum or minimum value in TXA. These results
are determined using the comparison rules specified in
Subclause 8.2, "<comparison predicate>".

iv) If SUM is specified, then the result is the sum of the
values in TXA. If the sum is not within the range of the
data type of the result, then an exception condition is
raised: data exception-numeric value out of range.

I regard this as pretty bogus --- having SUM of no rows return zero
would make more sense --- but that's the standard.

regards, tom lane