----- Original Message -----
From: "Martin Engelschalk" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Thursday, September 08, 2005 2:23 PM
Subject: Re: [sqlite] SUM and NULL values
SUM() itself does imply an numerical type return and does not need any
rows
from which to get a type - even more so in SQLite, where types are not a
big issue.
To return to the example "sales in october":
If there are no records for october, then there were no sales, and the sum
of - say - profits is obviously 0.
If there is a record for october, but is contains a NULL value for the
profits, this
means there is no data for an existing october sale, and sum() should
return NULL.
Lets take the October sales further. Lets say if there were any sales in
October, that you had to subtract $100 from the total sales for October to
account for ... lets say "shipping costs".
SELECT SUM(amt) - 100 from sales where month = 'october'
If there were no sales, under your query plan, I'd still have been in the
hole $100.
This is vastly oversimplified in order to show that "0" does not always
answer the question and can cascade into an even worse scenario.
Robert
