Postgres Pro
Downloads
Home   >   mailing lists

Thread: sum() - unexpected results.

sum() - unexpected results.

From
Jeff MacDonald
Date:
Hi,

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..

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

oh, btw this is pgsql 7.0.2 on fbsd.

anyone ?

Jeff MacDonald,

-----------------------------------------------------
PostgreSQL Inc        | Hub.Org Networking Services
jeff@pgsql.com        | jeff@hub.org
www.pgsql.com        | www.hub.org
1-902-542-0713        | 1-902-542-3657
-----------------------------------------------------
Facsimile : 1 902 542 5386
IRC Nick  : bignose
PGP Public Key : http://bignose.hub.org/public.txt

Re: sum() - unexpected results.

From
Tom Lane
Date:
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

Re: sum() - unexpected results.

From
Tod McQuillin
Date:
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