First of all, thanks for your help. I have discovered that the result from:
SELECT bank, SUM(unit_price_us * order_cbm) FROM lcopen GROUP BY bank; is different to the query you provided (row OPEN). How can I solve it? Terence Ng --- [EMAIL PROTECTED] wrote: > Hello Terence, > > I posted this to: > Newsgroups: <A > HREF="http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&group=mailing.database.mysql";>mailing.database.mysql</A> > Date: 2003-01-08 01:16:04 PST > Subject: Question - Query > > Perhaps you didn't see it:). > > Use a CASE statement to conditionally SUM the > amount_us values, > ie. SUM only when lcreceive.due_date < current_date > is true. > If false a null will be summed which SUM excludes. > > SELECT > lcopen.bank, > SUM(lcopen.unit_price_us*lcopen.order_cbm) AS > open, > SUM(CASE WHEN lcreceive.due_date < current_date > THEN lcreceive.amount_us END) AS newamount > FROM lcopen, lcreceive > WHERE lcopen.id=lcreceive.id > GROUP BY lcopen.bank; > > You could also use IF instead of CASE. > > But there could be a problem with both SUM's.MySql > returns a 0 when all values of expr in SUM(expr) are > null instead of a null value.This behavior is > different from Min,Max and Avg which all return null > and could lead to misleading > results/interpretations. > A workaround for returning a null instead of 0 for > SUM > in the case of all null values is to first check for > all nulls using an aggregate that will return a null > in > this case.First check for a null using an > appropriate > aggregate then compute the sum or return null.Here > MAX is used to check for null in both SUM > expressions. > > SELECT > lcopen.bank, > CASE WHEN MAX(lcopen.unit_price_us*lcopen.order_cbm) > IS NOT NULL > THEN SUM(lcopen.unit_price_us*lcopen.order_cbm) > ELSE NULL END * 7.8 AS > open, > CASE WHEN MAX(CASE WHEN lcreceive.due_date < > current_date > THEN lcreceive.amount_us END) IS NOT NULL > THEN SUM(CASE WHEN lcreceive.due_date < > current_date > THEN lcreceive.amount_us END) ELSE NULL END AS > newamount, > FROM lcopen, lcreceive > WHERE lcopen.id=lcreceive.id > GROUP BY lcopen.bank; > > You could also use count(expr) for testing nulls and > test for >0 > instead of IS NOT NULL. > You could also use IF instead of CASE.There are many > different > ways of writing the logic. > > MySql hopefully will fix the SUM function in the > case of all nulls. > All enterprise rdbms return null in this case:). > > www.rac4sql.net > _______________________________________________________________________ Do You Yahoo!? Get your free @yahoo.com.hk address at http://mail.english.yahoo.com.hk --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
