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 > 10000 > > (at least that is what the select * displays for the table) So I expected to > see the rolling "runbal" column to be: > -314.43 > 9999.97 > -0.03 > > what I actually got was > -314.43 > 10000 > -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) = 10000 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 10000 - 10000 which is 0 of course and the > last float would round to that. But of course I shouldn't be starting from > 10000. > > 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]
