D. Richard Hipp schrieb:
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.
Yes, I see - The <some-nulls> case escaped my notice in the heat of the
argument :-)
In any case, I would never build my queries to depend on the DB Engine
(not even the fantastic SQLite) in such a difficult case, but perhaps
check for null values first and use coalesce / nvl
Martin
