Better ifnull(sum(amount_paid),0) Because if you add up a column which contains NULL and NOT NULL values, all NULL values are SKIPPED for the calculation.
This implies that if the column ONLY contains NULL values, the result is NULL. Would be great if MySQL could post a chart somewhere of how they treat NULL values in calculations. Until then, check the release notes for every release concerning NULLs. And also the known bugs/future releases. Cheers /rudy -----Original Message----- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: vrijdag 18 juli 2003 12:43 To: [EMAIL PROTECTED] Subject: Re: join query result difference between 3.23.49 and 4.0.13 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
