RE: MySQL 5.1: incorrect arithmetic calculation
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:ad...@lissyara.su] Sent: Thursday, February 14, 2013 9:36 PM To: mysql@lists.mysql.com 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 | 1 | №20440 | 1.67 | 0.10 |365 | | 10551854 | 10369 | 497 | 2 | №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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL 5.1: incorrect arithmetic calculation
15.02.2013 15:07, Alex Keda пишет: OK. But, how about: mysql SELECT 365 * 1.67 * ( 1 - 0.10); +--+ | 365 * 1.67 * ( 1 - 0.10) | +--+ | 548.5950 | +--+ 1 row in set (0.00 sec) mysql ?? sorry, I'm too many work... =) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL 5.1: incorrect arithmetic calculation
- Original Message - From: Alex Keda ad...@lissyara.su mysql SELECT SUM(`Amount`*`Cost`*(1-`Discont`)) as `Summ` FROM `WorksCompliteAgregate` WHERE (`ContractID` = 10369 AND `Month` = 497); Based off the select you printed, this comes to EXACTLY 548.595 for the first row and 0 for the second row. mysql SELECT SUM(`Amount`*`Cost`*(1-`Discont`)*100)/100 as `Summ` FROM `WorksCompliteAgregate` WHERE (`ContractID` = 10369 AND `Month` = 497); The more detailed result here, though, seems to suggest that there's a longer fraction in your table than is printed by your select. Would your column happen to be a Float? but, my desktop calculator gives the result 548.60 Which is the correct rounding for 548.595. Check if your column is a float, and if it is, go google for floating point mathematics. They do not work the way you think they do. Use decimal(n,m) for money - or any discrete number, for that matter. Floats are not exact values, they are APPROXIMATE values. https://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html may also give you an idea of what goes wrong. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL 5.1: incorrect arithmetic calculation
15.02.2013 14:43, Johan De Meersman пишет: - Original Message - From: Alex Keda ad...@lissyara.su mysql SELECT SUM(`Amount`*`Cost`*(1-`Discont`)) as `Summ` FROM `WorksCompliteAgregate` WHERE (`ContractID` = 10369 AND `Month` = 497); Based off the select you printed, this comes to EXACTLY 548.595 for the first row and 0 for the second row. mysql SELECT SUM(`Amount`*`Cost`*(1-`Discont`)*100)/100 as `Summ` FROM `WorksCompliteAgregate` WHERE (`ContractID` = 10369 AND `Month` = 497); The more detailed result here, though, seems to suggest that there's a longer fraction in your table than is printed by your select. Would your column happen to be a Float? but, my desktop calculator gives the result 548.60 Which is the correct rounding for 548.595. Check if your column is a float, and if it is, go google for floating point mathematics. They do not work the way you think they do. Use decimal(n,m) for money - or any discrete number, for that matter. Floats are not exact values, they are APPROXIMATE values. https://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html may also give you an idea of what goes wrong. OK. But, how about: mysql SELECT 365 * 1.67 * ( 1 - 0.10); +--+ | 365 * 1.67 * ( 1 - 0.10) | +--+ | 548.5950 | +--+ 1 row in set (0.00 sec) mysql ?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL 5.1: incorrect arithmetic calculation
- Original Message - From: Alex Keda ad...@lissyara.su To: mysql@lists.mysql.com Sent: Friday, 15 February, 2013 12:16:18 PM Subject: Re: MySQL 5.1: incorrect arithmetic calculation sorry, I'm too many work... =) Heh :-) I was thinking, why would that not be correct? It's exactly what the desktop calculator gives upon copy/pasting the arithmetic. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL 5.1: incorrect arithmetic calculation
From: Alex Keda ad...@lissyara.su To: mysql@lists.mysql.com; Sent: 6:37 Piątek 2013-02-15 Subject: MySQL 5.1: incorrect arithmetic calculation ( ... cut ...) but, my desktop calculator gives the result 548.60 1. your desktop calculator is wrong 2. correct result is 548.595, variations (548.59 and 548.594985) are related to various data types and rounding related issues while multiplying and dividing by 100 http://dev.mysql.com/doc/refman/5.1/en/problems-with-float.html Regards, m -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
