Re: sum() - unexpected results.

Jeff MacDonald <jeff@hub.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