From: Date: July 17 2003 6:23pm
Subject: Re: join query result difference between 3.23.49 and 4.0.13
List-Archive: http://lists.mysql.com/mysql/145447
Message-Id: <5.1.0.14.2.20030717091800.00aae378@pop.west.cox.net>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed

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?

thanks in advance.

doug

At 02:53 PM 7/17/2003 +0000, mysql-digest-help@stripped wrote:
>-----Original Message-----
>From: Victoria Reznichenko [mailto:victoria.reznichenko@stripped]
>Sent: donderdag 17 juli 2003 10:33
>To: mysql@stripped
>Subject: Re: join query result difference between 3.23.49 and 4.0.13
>
>Doug Reese <getjazzed@stripped> 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.