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:victoria.reznichenko@stripped]
Sent: vrijdag 18 juli 2003 12:43
To: mysql@stripped
Subject: Re: join query result difference between 3.23.49 and 4.0.13
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=1
