On Thu, 2005-09-08 at 23:05 +0200, Martin Engelschalk wrote:
> I have found that, when dealing with NULL values, it helps to think of a
> Null as "I don't know, I have no data".
> So, if the where clause returns no records, I do know the result: It is 0.
> If there where clause returns records with NULL values, that means I did
> not know the values and so can
> not know the sum, too. Sum() should therefore return NULL.
> When I want to regard a NULL value as 0 in this (or any) context, I use
> a sum(coalesce(row, 0)).
>
Martin, your argument makes good sense. Unfortunately, that
is not what the SQL standard says should happen. According
to SQL (or at least the authorities on the subject I have
access to) any NULL values in the series input to SUM are
to be ignored. SUM returns the summation of the non-null
values. Or if there are no non-NULL values SUM returns NULL.
So SQL sez:
SUM(<empty-set>) == NULL
SUM(<all-nulls>) == NULL
SUM(<some-nulls>) == Sum of non-null values
Martin sez:
SUM(<empty-set>) == 0
SUM(<all-nulls>) == NULL
SUM(<some-nulls>) == NULL
SQLite 3.2.5 does this:
SUM(<empty-set>) == 0
SUM(<all-nulls>) == 0
SUM(<some-nulls>) == Sum of non-null values
I can understand Martin's point of view. The SQL standard
point of view makes no sense to me at all.
--
D. Richard Hipp <[EMAIL PROTECTED]>
