List:General Discussion« Previous MessageNext Message »
From:Rick James Date:February 21 2013 7:49pm
Subject:RE: MySQL 5.1: incorrect arithmetic calculation
View as plain text  
They are both right.  It is a matter of how many decimal places you want to display:

mysql> SELECT 365 * 1.67 * ( 1 - 0.10);
+--------------------------+
| 365 * 1.67 * ( 1 - 0.10) |
+--------------------------+
|                 548.5950 |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(365 * 1.67 * ( 1 - 0.10), 2);
+------------------------------------+
| ROUND(365 * 1.67 * ( 1 - 0.10), 2) |
+------------------------------------+
|                             548.60 |
+------------------------------------+
1 row in set (0.00 sec)

> -----Original Message-----
> From: Alex Keda [mailto:admin@stripped]
> Sent: Thursday, February 14, 2013 9:36 PM
> To: mysql@stripped
> Subject: MySQL 5.1: incorrect arithmetic calculation
> 
> bkp0# mysql h5000_bill
> Welcome to the MySQL monitor.  Commands end with ; or \g.
> Your MySQL connection id is 1643184
> Server version: 5.1.68-log FreeBSD port: mysql-server-5.1.68
> 
> Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights
> reserved.
> 
> Oracle is a registered trademark of Oracle Corporation and/or its
> affiliates. Other names may be trademarks of their respective owners.
> 
> Type 'help;' or '\h' for help. Type '\c' to clear the current input
> statement.
> 
> mysql> set names utf8;
> Query OK, 0 rows affected (0.00 sec)
> 
> mysql> SELECT * FROM `WorksCompliteAgregate` WHERE (`ContractID` =
> 10369
> AND `Month` = 497);
> +----------+------------+-------+-----------+----------+--------+------
> ---+--------+
> | ID       | ContractID | Month | ServiceID | Comment  | Cost   |
> Discont | Amount |
> +----------+------------+-------+-----------+----------+--------+------
> ---+--------+
> | 10551851 |      10369 |   497 |     10000 | №20440 |   1.67 | 0.10
> |    365 |
> | 10551854 |      10369 |   497 |     20000 | №20441 | 150.00 | 1.00
> |      1 |
> +----------+------------+-------+-----------+----------+--------+------
> ---+--------+
> 2 rows in set (0.00 sec)
> 
> mysql> SELECT SUM(`Amount`*`Cost`*(1-`Discont`)) as `Summ` FROM
> `WorksCompliteAgregate` WHERE (`ContractID` = 10369 AND `Month` = 497);
> +--------+
> | Summ   |
> +--------+
> | 548.59 |
> +--------+
> 1 row in set (0.00 sec)
> 
> mysql> SELECT SUM(`Amount`*`Cost`*(1-`Discont`)*100)/100 as `Summ` FROM
> `WorksCompliteAgregate` WHERE (`ContractID` = 10369 AND `Month` = 497);
> +------------+
> | Summ       |
> +------------+
> | 548.594985 |
> +------------+
> 1 row in set (0.00 sec)
> 
> mysql> SELECT 365 * 1.67 * ( 1 - 0.10);
> +--------------------------+
> | 365 * 1.67 * ( 1 - 0.10) |
> +--------------------------+
> |                 548.5950 |
> +--------------------------+
> 1 row in set (0.00 sec)
> 
> mysql>
> ===============
> 
> but, my desktop calculator gives the result 548.60
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql

Thread
MySQL 5.1: incorrect arithmetic calculationAlex Keda15 Feb
  • Re: MySQL 5.1: incorrect arithmetic calculationJohan De Meersman15 Feb
    • Re: MySQL 5.1: incorrect arithmetic calculationAlex Keda15 Feb
      • Re: MySQL 5.1: incorrect arithmetic calculationAlex Keda15 Feb
        • Re: MySQL 5.1: incorrect arithmetic calculationJohan De Meersman15 Feb
  • Re: MySQL 5.1: incorrect arithmetic calculationmisiaq15 Feb
  • RE: MySQL 5.1: incorrect arithmetic calculationRick James21 Feb