List:General Discussion« Previous MessageNext Message »
From:Kerry Frater Date:December 14 2006 11:21pm
Subject:RE: mysql v5 math a bit out. How do I round the info to become correct
View as plain text  
Thanks for the reference Jay.
Most helpful.

Kerry

-----Original Message-----
From: Jay Pipes [mailto:jay@stripped]
Sent: 14 December 2006 20:29
To: kerry@stripped
Cc: mysql@stripped
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:=@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
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/mysql?unsub=1

Thread
mysql v5 math a bit out. How do I round the info to become correctKerry Frater14 Dec
  • Re: mysql v5 math a bit out. How do I round the info to become correctJay Pipes14 Dec
    • RE: mysql v5 math a bit out. How do I round the info to become correctKerry Frater15 Dec