Re: join query result difference between 3.23.49 and 4.0.13

[Victoria Reznichenko]

Doug Reese <getjazzed@stripped> 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, 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.
> 
> 


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   Victoria.Reznichenko@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
       <___/   www.mysql.com