SUM() of one row != to actual value

[Bil]

I've run across a problem that has exposed a hole in my understanding of MySQL, I'm hoping you experts can set me straight. I have a query that gets the sum of a column

Table `costs_payments`
     cost_id int(11)
     payment_id int(11)
     amount float

SELECT SUM(amount) FROM costs_payments WHERE cost_id = [variable]

When the query matches only one row, however, the sum is not as precise as the value in the amount column for that one row. For example:

SELECT * FROM costs_payments WHERE cost_id = 43

returns one row:

cost_id     payment_id     amount
   43           23           34.92


SELECT SUM(amount) FROM costs_payments WHERE cost_id = 43

SUM(amount)
 34.91988856

Is this some internal MySQL storage thing I don't know about? Any ideas? Thanks!

[longneck]

no, that's just the nature of float (i.e., single and double). if you're storing money, you should be using decimal instead.

[r937]

Is this some internal MySQL storage thing I don't know about?

that depends on if you know about FLOAT and that its values are approximations

wouldn't DECIMAL(13,4) do just as well for monetary amounts?

[r937]

oops, sniped -- i was away from the computer and then posted without refreshing

[Bil]

Thanks guys. I was using Float instead of Decimal because I was under the impression that Ruby on Rails didn't do Decimal types (I'm going to look into this, though, because I may have made this up in my head)

Thanks again!

[longneck]

float and decimal are both returned to the host langauge in a format the host language can understand; it's the responsibility of the host language's mysql driver to make sure this happens transparently. i have no doubt that ruby can handle a decimal data type, probably as a float.