On 21 Dec 2006 07:03:22 -0800, "com" <com>
wrote:
Here's my results of:
SELECT VERSION();
SELECT ((200.45 * 1) * (10/100)),ROUND( (200.45 * 1 * (10/100)) ,2)
Linux
5.0.24a
20.45000 | 20.05
Windows
5.0.24-community-nt
20.45000 | 20.05
Can some one plz explain this??? SELECT ((200.45 * 1) * (10/100)),ROUND( (200.45 * 1 * (10/100)) ,2); Linux (Servidor Mysql 4) 20.04 | 20.04 Windows (Servidor Mysql 4) 20.05 | 20.05 Thanks...
Can some one plz explain this???
SELECT ((200.45 * 1) * (10/100)),ROUND( (200.45 * 1 * (10/100)) ,2);
Linux (Servidor Mysql 4)
20.04 | 20.04
Windows (Servidor Mysql 4)
20.05 | 20.05
Thanks
On 21 Dec 2006 07:03:22 -0800, "com" <com>
wrote:
Here's my results of:
SELECT VERSION();
SELECT ((200.45 * 1) * (10/100)),ROUND( (200.45 * 1 * (10/100)) ,2)
Linux
5.0.24a
20.45000 | 20.05
Windows
5.0.24-community-nt
20.45000 | 20.05
Lee Peedin wrote:
>
>
> Here's my results of:
> SELECT VERSION();
> SELECT ((200.45 * 1) * (10/100)),ROUND( (200.45 * 1 * (10/100)) ,2)
>
> Linux
> 5.0.24a
> 20.45000 | 20.05
>
> Windows
> 5.0.24-community-nt
> 20.45000 | 20.05[/ref]
Linux 4.1.16
20.04 | 20.04
Linux 5.0.24-standard-log
20.045000 | 20.05
--
Brian Wakem
Email: http://homepage.ntlworld.com/b.wakem/myemail.png
Using the command with version heres the outputs
SELECT VERSION( ) , (( 200.45 *1 ) * ( 10 /100 )), ROUND( ( 200.45 *1 *
( 10 /100 ) ) , 2 ) , ROUND( 20.045, 2 ) ;
Windows 2003
5.0.22-community-nt 20.045000 20.05 20.05
Windows 2003
4.1.18-nt-max 20.05 20.05 20.05
Centos
4.1.21-standard 20.04 20.04 20.05
FreeBSD
3.23.58 20.05 20.05 20.05
After all my testing i've found that the only system that does the
Round and the math good is windows.
On Dec 22, 6:03 pm, Brian Wakem <com> wrote:
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
>
>
>
> 20.04 | 20.04
>
> Linux 5.0.24-standard-log
> 20.045000 | 20.05
>
> --
> Brian Wakem
> Email:http://homepage.ntlworld.com/b.wakem/myemail.png[/ref]
It must be something with the libs im using on the background can any
one tell me witch ones do i need to change or point me to some website
that explains / solves this problem.
On Dec 26, 11:32 am, "com" <com> wrote: [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
>
> > 20.04 | 20.04[/ref]
>
> [/ref]
com wrote:
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> [/ref]
>
>[/ref]
It's not a a "problem" - it's how floating point numbers are stored, and
the results for your operation will always be somewhat unpredictable.
That's true not only in SQL - but in any language which uses floating
point numbers.
The reason this happens is that 20.045 is not exactly 20.045. If you
print it out to enough decimal places, it will come out to something
like 20.04499999967. Rounding this value would (correctly) give a
result of 20.04.
The same thing happens in decimal if you try adding 1/3 3 times. You
get .9999999... instead of 1 because 1/3 cannot be expressed directly in
decimal form.
The way to resolve it is to add a small value (smaller than the rounding
error) to the number. In your case a value of .001 should suffice
(since you're rounding to 2 decimal places).
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
net
==================
Any calculator is able to return the same value has the above.
The so special calc that i am making is quite simple.
200.45 is the price
1 = quantity
10 is the discount
((200.45 * 1) * (10/100))
Price * Qt. * (Discount/100)
I am trying to get how much is the Discount in Price * Qt.
200.45 * 10% gives very simple result ( 200.45 * 0.10 ) = 20.045
the only Mysql Servers that returns the right value are
5.0.22-community-nt 20.045000 20.05 20.05
5.0.22-standard 20.045000 20.05 20.05
After all my tests it seems that i am going to mysql 5 sooner than
expected since it does all the math correctly.
Thanks for all the help. Hope this can also helps someone else.
Best Regards
Luis Miguel Freitas
On Dec 26, 2:22 pm, Jerry Stuckle <net> wrote:
>
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> the results for your operation will always be somewhat unpredictable.
> That's true not only in SQL - but in any language which uses floating
> point numbers.
>
> The reason this happens is that 20.045 is not exactly 20.045. If you
> print it out to enough decimal places, it will come out to something
> like 20.04499999967. Rounding this value would (correctly) give a
> result of 20.04.
>
> The same thing happens in decimal if you try adding 1/3 3 times. You
> get .9999999... instead of 1 because 1/3 cannot be expressed directly in
> decimal form.
>
> The way to resolve it is to add a small value (smaller than the rounding
> error) to the number. In your case a value of .001 should suffice
> (since you're rounding to 2 decimal places).
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> net
> ==================[/ref]
com wrote:
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>the results for your operation will always be somewhat unpredictable.
>>That's true not only in SQL - but in any language which uses floating
>>point numbers.
>>
>>The reason this happens is that 20.045 is not exactly 20.045. If you
>>print it out to enough decimal places, it will come out to something
>>like 20.04499999967. Rounding this value would (correctly) give a
>>result of 20.04.
>>
>>The same thing happens in decimal if you try adding 1/3 3 times. You
>>get .9999999... instead of 1 because 1/3 cannot be expressed directly in
>>decimal form.
>>
>>The way to resolve it is to add a small value (smaller than the rounding
>>error) to the number. In your case a value of .001 should suffice
>>(since you're rounding to 2 decimal places).
>>
>>--
>>==================
>>Remove the "x" from my email address
>>Jerry Stuckle
>>JDS Computer Training Corp.
>>net
>>==================[/ref]
>
>
> Any calculator is able to return the same value has the above.
>
> The so special calc that i am making is quite simple.
>
> 200.45 is the price
> 1 = quantity
> 10 is the discount
>
> ((200.45 * 1) * (10/100))
> Price * Qt. * (Discount/100)
>
> I am trying to get how much is the Discount in Price * Qt.
>
> 200.45 * 10% gives very simple result ( 200.45 * 0.10 ) = 20.045
>
> the only Mysql Servers that returns the right value are
> 5.0.22-community-nt 20.045000 20.05 20.05
> 5.0.22-standard 20.045000 20.05 20.05
>
> After all my tests it seems that i am going to mysql 5 sooner than
> expected since it does all the math correctly.
>
> Thanks for all the help. Hope this can also helps someone else.
> Best Regards
> Luis Miguel Freitas
>[/ref]
(Top posting fixed)
"Any calculator" is not the same as a computer. This problem has
existed since the dawn of computing - I first saw it when I started with
Fortran in 1967. That's why there are DECIMAL data types - the data is
internally stored in a different format, which does have exact decimal
values.
But floating point values all suffer this problem - in all computers,
big and small.
And yes, you might have seen a version of MySQL 5.0 which "solves" this
problem - but it is not a "guaranteed" fix, because it's not a
"problem". ANY time you round as closely as you are doing, the results
will be indeterminate. The same is true with truncating;
0.2 * 5 does not necessarily equal 1.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
net
==================
On 26 Dec 2006 07:09:29 -0800, "com" <com>
wrote:
If you need 100% accuracy don't use floating point. You should be able
to figure out a way to do what you want with integers, integer division,
mod (modulo), etc.
--
Dennis K.
So your saying that theres no way to "fix" this "problem" you have to
look at the values and get make the necessary changes.
But shouldn't mysql server 4 "fix" the "problem" like in supposedly in
other systems like Mysql 5 and Oracle.
I've tested some values in oracle and all so excel and i allways get
On Dec 26, 3:58 pm, Jerry Stuckle <net> wrote:
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
>
> >
> > The so special calc that i am making is quite simple.
> >
> > 200.45 is the price
> > 1 = quantity
> > 10 is the discount
> >
> > ((200.45 * 1) * (10/100))
> > Price * Qt. * (Discount/100)
> >
> > I am trying to get how much is the Discount in Price * Qt.
> >
> > 200.45 * 10% gives very simple result ( 200.45 * 0.10 ) = 20.045
> >
> > the only Mysql Servers that returns the right value are
> > 5.0.22-community-nt 20.045000 20.05 20.05
> > 5.0.22-standard 20.045000 20.05 20.05
> >
> > After all my tests it seems that i am going to mysql 5 sooner than
> > expected since it does all the math correctly.
> >
> > Thanks for all the help. Hope this can also helps someone else.
> > Best Regards
> > Luis Miguel Freitas
> >[/ref]
>
> (Top posting fixed)
>
> "Any calculator" is not the same as a computer. This problem has
> existed since the dawn of computing - I first saw it when I started with
> Fortran in 1967. That's why there are DECIMAL data types - the data is
> internally stored in a different format, which does have exact decimal
> values.
>
> But floating point values all suffer this problem - in all computers,
> big and small.
>
> And yes, you might have seen a version of MySQL 5.0 which "solves" this
> problem - but it is not a "guaranteed" fix, because it's not a
> "problem". ANY time you round as closely as you are doing, the results
> will be indeterminate. The same is true with truncating;
>
> 0.2 * 5 does not necessarily equal 1.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> net
> ==================[/ref]
Removed by Administrator
Bookmarks