Thanks for the reference Jay.
From: Jay Pipes [mailto:jay@stripped]
Sent: 14 December 2006 20:29
Subject: Re: mysql v5 math a bit out. How do I round the info to become
For exact calculations, you need to use the DECIMAL data type. See this
section in the manual for the reasons why:
Kerry Frater wrote:
> I am running a small procedure for set jobs that calculates a running
> 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:=@rbal+creditamount+(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
> DebitAmount CreditAmount
> (at least that is what the select * displays for the table) So I expected
> see the rolling "runbal" column to be:
> what I actually got was
> Now I can understand some to be rounding errors and I would like to know
> 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
> concern. The last calculated value of "runbal" could be explained by the
> 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
> Thanks for any advice
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql