From: Date: July 18 2003 2:07pm
Subject: RE: join query result difference between 3.23.49 and 4.0.13
List-Archive: http://lists.mysql.com/mysql/145510
Message-Id: <E8E4CBA43FBA2C4A9074D4CE00B9A48C0EF2DA@pareto01.pareto.local>
MIME-Version: 1.0
Content-Type: text/plain;	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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.=20

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]=20
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,
>=20
> 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=20
> 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,=20
> 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))

>=20
> doug
>=20
> 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=3Dbilling_payment.invoice ) WHERE
billing.invoice=3D10001
>>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.
>=20
>=20


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





--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=3Drudy.metzger@stripped