On Thu, 08 Sep 2005 16:24:14 -0400, "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:
> Can somebody come up with a realistic scenario where they would
> actually want SUM() to return NULL instead of 0? NULL is such
> a profoundly broken return value for sum() in my mind that I'm
> thinking of ignoring the standard and just coding SQLite to do
> the Right Thing. But I am open to the possibility that there
> are some cases outside of my imagination where returning zero
> might be considered "wrong".
Isn't the meaning of 0 part of the application logic? If you really are
going to do it, can there be a pragma to switch the behaviour?
> If nobody can suggest a scenario where SUM() returning NULL is
> actually helpful, then I will likely return SQLite to its former
> mode of operation which is to have SUM() return 0 when it has no
> input.
What about when the sum is on a field in a left joined table?
create table items
(item_code char(10), description varchar(30));
insert into items values('WIDGETS','WIDGET 3.5 inch');
insert into items values('BOXES','Box 2 inch');
insert into items values('ETCHA','Etch-a-sketch');
create table transactions
(item_code char(10),
customer_code char(10),
purchase_date date,
amt numeric);
-- customer buys widget
insert into transactions('WIDGETS','CASH','20050905',11.00);
-- customer brings widget back
insert into transactions('WIDGETS','CASH','20050907,'-11.00);
-- customer from last month brings box back
insert into transactions('BOXES','CASH','20050907,'-20.00);
-- monthly profitiblity report
select items.item_code,
sum(transactions.amt), count (transactions.item_code)
>from items
left outer join transactions on transactions.item_code = items.item_code
where date >= '20050901' and date <= '20050930'
order by 3 desc, 4 desc
I want the result to be descending money, then descending counts, with
no activity items at the end.
klint.
+---------------------------------------+-----------------+
: Klint Gore : "Non rhyming :
: EMail : [EMAIL PROTECTED] : slang - the :
: Snail : A.B.R.I. : possibilities :
: Mail University of New England : are useless" :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : +61 2 6772 5376 : :
+---------------------------------------+-----------------+
