Doug Reese <[EMAIL PROTECTED]> wrote: > hello victoria, > > thanks for your response. however, the real goal of my question still > remains unanswered: given my sample data, how would i find the balance on > an invoice with one sql statement. sure, i could query for the amount > paid, then in a separate query subtract the result from the invoice total, > but that seems like a step backwards. is there a way to do what i was > doing with v3.23, given the new NULL behavior?
Check if field has NULL value, then 0, else original value. SUM(if(amount_paid IS NULL, 0, amount_paid)) > > doug > > At 02:53 PM 7/17/2003 +0000, [EMAIL PROTECTED] wrote: >>-----Original Message----- >>From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] >>Sent: donderdag 17 juli 2003 10:33 >>To: [EMAIL PROTECTED] >>Subject: Re: join query result difference between 3.23.49 and 4.0.13 >> >>Doug Reese <[EMAIL PROTECTED]> wrote: >> > >> > query #2 >> > mysql> SELECT billing.invoice, SUM(amount_paid) AS paid, >>(billing.amount - >> > SUM(amount_paid)) AS balance FROM billing LEFT JOIN billing_payment ON >>( >> > billing.invoice=billing_payment.invoice ) WHERE billing.invoice=10001 >>GROUP >> > BY billing_payment.invoice; >> > +---------+------+---------+ >> > | invoice | paid | balance | >> > +---------+------+---------+ >> > | 10001 | NULL | NULL | >> > +---------+------+---------+ >> > 1 row in set (0.00 sec) >> > >> > NULL values in this result are not expected, nor are they helpful in >> > determining the invoice balance. >> >>It's correct result. Since 4.0.13 SUM() returns NULL if there is no rows >>in the result or if all values are NULL (as in your case). >>And SELECT billing.amount - NULL also returns NULL. > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
