Robert Simpson wrote:
SUM without any rows should return NULL. It had no type affinity with
which to obtain an answer for the question, and no meaningful rows
were available with which to glean an answer.
If SUM returns 0 when no rows were evaluated, then SQLite is making an
assumption about what the answer should be, which is incorrect.
SELECT SUM(temperature) FROM antartica WHERE temperature < -150 LIMIT 1
Does it makes sense for that query to return 0 if no rows matched the
criteria? If SQLite doesn't know the answer definitively, then it
shouldn't return one.
Robert
I am sorry, but I do not agree.
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.
Martin
PS: I am aware that the discussion about NULL is an old one and divides
the world in warring factions :-)
