If "SELECT SUM(amt)" means "How much did I sell?"
Then the "NULL" should mean, "You didn't sell anything.", no?
To me, there is a difference between 0 ("You sold merchandise worth $0"
perhaps because of sweepstakes, giveaway, rebate coupons, etc) and NULL
("You didn't sell anything.").
I think that Robert Simpson nailed on the head with his salary example.
If you want a quick report on sales per month:
SELECT month, COALESCE(SUM(amount), 'No Sales') from table GROUP BY month
If you insist that you want the result to be zero rather than NULL:
SELECT month, COALESCE(SUM(amount), 0) from table GROUP BY month
The behavior according to the SQL standard allows for flexibility.
-----Original Message-----
From: Ted Unangst [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 08, 2005 6:21 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SUM and NULL values
*snip*
I would think "SELECT SUM(amt)" means "How much did I sell?" or "What
was my income?", and a better query here would be "SELECT SUM(amt) -
SUM(costs)". Just because there were no sales doesn't mean that the
profit (or loss) is indeterminate. If your boss asked you "How much did
you make in October?" the answer is not "I can't tell."
Exacting correctness aside, I would expect 0 to confuse fewer people.
Is it more likely an SQL expert relies on NULL being different than 0,
or that a non-expert selects sum() without considering the NULL possibility?
Perhaps a pragma is the right answer?
--
Ted Unangst www.coverity.com Coverity, Inc.
