double vs. float

I've inherited a mysql (5.x) table that stores amounts as a double in
a column.

If I sum(tbl_ra_amount) as amt for a particular user, I can get a 2
decimal number: 521.93

Now if I want to zero that account out, I insert another transaction
using the negative of the sum: -521.93, but I'm getting some huge
decimal remainders: 1.13686837722.

I've tried an experiment where I first updated the values for this
user using update... set tbl_ra_amount = round(tbl_ra_amount,2) where
user etc. - but that's not giving me a nice 2 decimal amount to
subtract.

Is there a way to either truly convert all of the amounts to rounded 2
decimal amounts (which is fine - we don't need more precision than
that), or, figure out what the true amount to subtract would be to
give me a 0 "balance"?

On Thu, 17 Jan 2008 06:47:34 -0800 (PST), Steve wrote:
> I've inherited a mysql (5.x) table that stores amounts as a double in
> a column.
>
> If I sum(tbl_ra_amount) as amt for a particular user, I can get a 2
> decimal number: 521.93
>
> Now if I want to zero that account out, I insert another transaction
> using the negative of the sum: -521.93, but I'm getting some huge
> decimal remainders: 1.13686837722.
>
> I've tried an experiment where I first updated the values for this
> user using update... set tbl_ra_amount = round(tbl_ra_amount,2) where
> user etc. - but that's not giving me a nice 2 decimal amount to
> subtract.
>
> Is there a way to either truly convert all of the amounts to rounded 2
> decimal amounts (which is fine - we don't need more precision than
> that), or, figure out what the true amount to subtract would be to
> give me a 0 "balance"?

Sure, put them all in DECIMAL instead of the approximation types like
FLOAT and DOUBLE. See
http://dev.mysql.com/doc/refman/5.0/...ric-types.html

--
Give a man a fish and you feed him for a day; give him a freshly-charged
Electric Eel and chances are he won't bother you for anything ever again.
-- Tanuki