[Fwd: Re: SUM(field) sometimes returns -0 << THANK YOU >>]

-------- Original Message --------
Subject: Re: SUM(field) sometimes returns -0 << THANK YOU >>
Date: Thu, 28 Aug 2003 21:23:17 +1000
From: Michael Duncan
To: Alexander Keremidarski
References: <3F4D6452.30608@generyx.net.au> <3F4DCD6D.30705@mysql.com>

Thanks for flash of inspiration - if I use ROUND(SUM(amount),2) it works
as expected.

mysql> SELECT unit_num, ROUND(SUM(amount),2) AS tot FROM journal_1 GROUP
BY unit_num HAVING tot<0 ORDER BY unit_num;
+----------+---------+
| unit_num | tot |
+----------+---------+
| 5 | -73.79 |
| 7 | -173.32 |
| 13 | -173.32 |
| 18 | -0.03 |
+----------+---------+
4 rows in set (0.00 sec)


I had suspected a floating point issue (thats why in the third query the
table had magically grown an extra decimal place), however the field is
type DECIMAL(10,2) and all data entered has only 2 decimal points with
no division/multiplication to mess with the decimal portion(s).

For the sake of completeness, this is the result from the query you
suggested.

mysql> SELECT unit_num, SUM(amount) AS tot, ROUND(SUM(amount), 10) as prec
-> FROM journal_1
-> GROUP BY unit_num
-> HAVING tot<0
-> ORDER BY unit_num;
+----------+---------+-----------------+
| unit_num | tot | prec |
+----------+---------+-----------------+
| 5 | -73.79 | -73.7900000000 |
| 6 | -0.00 | -0.0000000000 |
| 7 | -173.32 | -173.3200000000 |
| 9 | -0.00 | -0.0000000000 |
| 13 | -173.32 | -173.3200000000 |
| 15 | -0.00 | -0.0000000000 |
| 17 | -0.00 | -0.0000000000 |
| 18 | -0.03 | -0.0300000000 |
+----------+---------+-----------------+
8 rows in set (0.00 sec)


Thanks again for the prompt and inpirational reply.

Alexander Keremidarski wrote:
> Michael,
> Michael Duncan wrote:
>
>> While developing an app I came across this anomoly.
>>
>> Just to make sure it wasn't VB/ODBC/Win2K/network/exteneral gremlin/...
>> I did the query on the console of the machine running mySQL.
>> There are 18 units in this table the ones not listed in the first
>> query do return a valid 0 (+0) balance.
>>
>> I can allow for this by double checking the returned data in my app
>> but it shouldn't be happening should it?
>>
>>
>> mysql> SELECT unit_num, SUM(amount) AS tot FROM journal_1 GROUP BY
>> unit_num HAVING tot<0 ORDER BY unit_num;
>> +----------+---------+
>> | unit_num | tot |
>> +----------+---------+
>> | 5 | -73.79 |
>> | 6 | -0.00 | <- is -0<0 ? I suppose it must be
>> | 7 | -173.32 |
>> | 9 | -0.00 |
>> | 13 | -173.32 |
>> | 15 | -0.00 |
>> | 17 | -0.00 |
>> | 18 | -0.03 |
>> +----------+---------+
>> 8 rows in set (0.01 sec)
>
>
> If your column amount is FLOAT this is normal result. This is just hou
> floating point arithmetic works.
>
> It may happen that sum is: -0.00001234 which will be rounded to -0.00
> not 0.00
> Try simple check:
>
> SELECT unit_num, SUM(amount) AS tot, ROUND(SUM(amount), 10) as prec
> FROM journal_1
> GROUP BY unit_num
> HAVING tot<0
> ORDER BY unit_num;
>
> Best regards
>




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org