For some reason I have to agree with the SQL standard, which I would
interprete as the following (and this may not be all that bullet proof):
"If the result set contains numeric values, sum them up, ignoring NULLs. If
there are no numeric values present (the result set is either empty or
contains only NULLs) return NULL."
There's a fine difference between NULL and 0. If I have an unordered result
set of:
a |b
----------
NULL |NULL
NULL |NULL
NULL |NULL
0 |NULL
NULL |NULL
I would want SUM(a) to be zero, but I'd want SUM(b) to be NULL. Just like
I'd want MIN(a) to return zero, but MIN(b) to be NULL.
If SUM() were to always return 0, wouldn't it break the logic of how it
behaved compared to its MIN(), MAX() and AVG() siblings?
Always returning at least 0 because it makes sense to me would not be enough
for me to justify breaking away from the standard, particularly because it's
so easy to ensure that the result will be 0 and not NULL as Martin mentioned
using "sum(coalesce(row, 0))".
And after all -- it's the standard. And absolutely I love using SQLite
simply because it (seems to) follow the standard so closely. The introduced
oh-so-subtle difference for developers ("MySQL, PostgreSQL, etc. etc. return
NULL, but SQLite actually defies the SQL standard and returns 0") could be
rather confusing.
I'm by no means an expert or authority when it comes to SQL, but NULL is
rather interesting. Since "NULL is always false even to itself" I think that
by having SUM(b) return NULL this "quirkiness" or "speciality" behavior of
NULL is preserved. Having the SUM() of a bunch of NULLs be 0 seems a bit too
magic to me.
-----Original Message-----
From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 08, 2005 5:17 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SUM and NULL values
*snip*
So SQL sez:
SUM(<empty-set>) == NULL
SUM(<all-nulls>) == NULL
SUM(<some-nulls>) == Sum of non-null values
*snip*
SQLite 3.2.5 does this:
SUM(<empty-set>) == 0
SUM(<all-nulls>) == 0
SUM(<some-nulls>) == Sum of non-null values
