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