[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 <[email protected]>
To: Alexander Keremidarski <[email protected]>
References: <[email protected]> <[email protected]>

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/[email protected]