Re: mysql v5 math a bit out. How do I round the info to become correct
For exact calculations, you need to use the DECIMAL data type. See this section in the manual for the reasons why: http://dev.mysql.com/doc/refman/5.1/en/precision-math-examples.html Cheers, Jay Kerry Frater wrote: I am running a small procedure for set jobs that calculates a running total for me to display. It works fine in most cases but the math concerns me. I am using 5.0.18 on my Windows PC and the latest 5.x on my Linux server (both test machines). The finance table is an InnoDb table. CreditAmount and Debitamount are both fields set to FLOAT. The SQL code called from my program select f.*,@rbal:[EMAIL PROTECTED](debitamount * -1) as runbal from (select @rbal:=0) rb,finance f where f.jobref='abc1234' order by f.jobref,f.inputorder I have one combination that has as data the following (listed in InputOrder) DebitAmount CreditAmount 314.43 10314.4 1 (at least that is what the select * displays for the table) So I expected to see the rolling runbal column to be: -314.43 .97 -0.03 what I actually got was -314.43 1 -0.000305176 Now I can understand some to be rounding errors and I would like to know how to tell MySQL that I am only interested to two decimal places in the evaluated variable but the math of (-314.43 + 10314.4) = 1 is more of a concern. The last calculated value of runbal could be explained by the use of float as it is trying to do 1 - 1 which is 0 of course and the last float would round to that. But of course I shouldn't be starting from 1. Thanks for any advice Kerry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql v5 math a bit out. How do I round the info to become correct
Thanks for the reference Jay. Most helpful. Kerry -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: 14 December 2006 20:29 To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: mysql v5 math a bit out. How do I round the info to become correct For exact calculations, you need to use the DECIMAL data type. See this section in the manual for the reasons why: http://dev.mysql.com/doc/refman/5.1/en/precision-math-examples.html Cheers, Jay Kerry Frater wrote: I am running a small procedure for set jobs that calculates a running total for me to display. It works fine in most cases but the math concerns me. I am using 5.0.18 on my Windows PC and the latest 5.x on my Linux server (both test machines). The finance table is an InnoDb table. CreditAmount and Debitamount are both fields set to FLOAT. The SQL code called from my program select f.*,@rbal:[EMAIL PROTECTED](debitamount * -1) as runbal from (select @rbal:=0) rb,finance f where f.jobref='abc1234' order by f.jobref,f.inputorder I have one combination that has as data the following (listed in InputOrder) DebitAmount CreditAmount 314.43 10314.4 1 (at least that is what the select * displays for the table) So I expected to see the rolling runbal column to be: -314.43 .97 -0.03 what I actually got was -314.43 1 -0.000305176 Now I can understand some to be rounding errors and I would like to know how to tell MySQL that I am only interested to two decimal places in the evaluated variable but the math of (-314.43 + 10314.4) = 1 is more of a concern. The last calculated value of runbal could be explained by the use of float as it is trying to do 1 - 1 which is 0 of course and the last float would round to that. But of course I shouldn't be starting from 1. Thanks for any advice Kerry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
