SEO

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, "kkmigas@gmail.com" <kkmigas@gmail.com>
wrote:

>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


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:

> On 21 Dec 2006 07:03:22 -0800, "kkmigas@gmail.com" <kkmigas@gmail.com>
> wrote:
>
>>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
>
>
> 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


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 <n...@email.com> wrote:
> Lee Peedin wrote:
> > On 21 Dec 2006 07:03:22 -0800, "kkmi...@gmail.com" <kkmi...@gmail.com>
> > wrote:
>
> >>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
>
> > 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.05Linux 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

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, "kkmi...@gmail.com" <kkmi...@gmail.com> wrote:
> 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 <n...@email.com> wrote:
>
> > Lee Peedin wrote:
> > > On 21 Dec 2006 07:03:22 -0800, "kkmi...@gmail.com" <kkmi...@gmail.com>
> > > wrote:
>
> > >>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
>
> > > 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.05Linux 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

kkmigas@gmail.com wrote:
> 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, "kkmi...@gmail.com" <kkmi...@gmail.com> wrote:
>
>>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 <n...@email.com> wrote:
>>
>>
>>>Lee Peedin wrote:
>>>
>>>>On 21 Dec 2006 07:03:22 -0800, "kkmi...@gmail.com" <kkmi...@gmail.com>
>>>>wrote:
>>
>>>>>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
>>
>>>>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.05Linux 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
>
>

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.
jstucklex@attglobal.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 <jstuck...@attglobal.net> wrote:
> kkmi...@gmail.com wrote:
> > 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, "kkmi...@gmail.com" <kkmi...@gmail.com> wrote:
>
> >>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 <n...@email.com> wrote:
>
> >>>Lee Peedin wrote:
>
> >>>>On 21 Dec 2006 07:03:22 -0800, "kkmi...@gmail.com" <kkmi...@gmail.com>
> >>>>wrote:
>
> >>>>>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
>
> >>>>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.05Linux 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.pngIt'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.
> jstuck...@attglobal.net
> ==================

kkmigas@gmail.com wrote:
> On Dec 26, 2:22 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>
>>kkmi...@gmail.com wrote:
>>
>>>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, "kkmi...@gmail.com" <kkmi...@gmail.com> wrote:
>>
>>>>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 <n...@email.com> wrote:
>>
>>>>>Lee Peedin wrote:
>>
>>>>>>On 21 Dec 2006 07:03:22 -0800, "kkmi...@gmail.com" <kkmi...@gmail.com>
>>>>>>wrote:
>>
>>>>>>>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
>>
>>>>>>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.05Linux 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.pngIt'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.
>>jstuck...@attglobal.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
>

(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.
jstucklex@attglobal.net
==================

On 26 Dec 2006 07:09:29 -0800, "kkmigas@gmail.com" <kkmigas@gmail.com>
wrote:

>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

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 <jstuck...@attglobal.net> wrote:
> kkmi...@gmail.com wrote:
> > On Dec 26, 2:22 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>
> >>kkmi...@gmail.com wrote:
>
> >>>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, "kkmi...@gmail.com" <kkmi...@gmail.com> wrote:
>
> >>>>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 <n...@email.com> wrote:
>
> >>>>>Lee Peedin wrote:
>
> >>>>>>On 21 Dec 2006 07:03:22 -0800, "kkmi...@gmail.com" <kkmi...@gmail.com>
> >>>>>>wrote:
>
> >>>>>>>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
>
> >>>>>>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.05Linux 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.pngIt'snot 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.
> >>jstuck...@attglobal.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
> >
>
> (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.
> jstuck...@attglobal.net
> ==================