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?
thanks in advance.
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.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
