Re: SUM(field) sometimes returns -0

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

-- 
  Are you MySQL certified? -> http://www.mysql.com/certification
  For technical support contracts, visit https://order.mysql.com/?ref=msal
     __  ___     ___ ____  __
    /  |/  /_ __/ __/ __ \/ /    Mr. Alexander Keremidarski <[email protected]>
   / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Full-Time Developer
  /_/  /_/\_, /___/\___\_\___/   Sofia, Bulgaria
          <___/  www.mysql.com




-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]