$10
How do I sum float fields?

This is a minor annoyance, but I am curious about it. Suppose I want to find out how much Darren and I have so far been paid for running WP Questions. To start with, I might type some simple SQL like this:

SELECT SUM( gross_payment_amount ) AS total

FROM  `question` 

WHERE 

STATUS =  'active'

OR STATUS =  'completed'

I get:

4413.5400061607

That is obviously not accurate. We've been paid in US money, and the smallest unit is a penny. The field gross_payment_amount is a float. Why can't the sum() function correctly add up a float field? Is there another way of adding float fields?

The issue isn't serious because I think MySql is only wrong by a penny, but how should I calculate that? Should I break out a calculator and add it up manually? That option seems insane when I have the awesome power of MySql at my finger tips, yet I have not yet figured out how to get it to add up floats correctly.

---------------------------

Perhaps the only way to get the numbers accurately are to add them up one at a time, outside of MySql, perhaps in my PHP code? Curious if anyone can suggest a rounding strategy that minimizes errors?

Lawrence Krubner | 05/09/10 at 12:54pm | Edit

addition, floats, sum

(3) Possible Answers Submitted...

Last edited:
05/13/10
6:30pm
Scott Meves says:
Floats are a little tricky in MySQL because they are actually stored as approximate values in the database. For fields that contain currency I like to use a DECIMAL field. In MySQL these are accurate up to 65 places, but I usually define them like:
```
...gross_payment_amount DECIMAL(10,2)
```
where 10 means we have 10 total digits, 2 of which are after the decimal point.

These are stored accurately and you won't have the rounding errors when you sum them.

If you want to try this on a test database, run the following queries:
```
ALTER TABLE `question` CHANGE `gross_payment_amount` `gross_payment_amount ` DECIMAL(10,2) DEFAULT 0;
```
And then try running your query again.

If you don't want to change your database schema, then the best you can do is round your results to two decimal places:
```
SELECT ROUND(SUM( gross_payment_amount ), 2) AS total

FROM  `question` 

WHERE 

STATUS =  'active'

OR STATUS =  'completed'
```
Good luck!
- 05/09/10 1:56pm
  
  Lawrence Krubner says:
  I guess my concern with rounding is that eventually a penny might get lost, or over time, many pennies. I've visions of "Office Space" type errors. Any thoughts about how to keep track of any rounding errors that might happen?
Last edited:
05/13/10
6:30pm
Nathan Briggs says:
The only way to guarantee not to get rounding errors over time is not to introduce them in the first place.

Do your calculations in the code with rounded off numbers, with whatever method your government prefers (probably either discard the fraction of a penny, or use normal round up/down rules from maths). In PHP, the function round will do this, eg round($fee, 2) for a 2 decimal places.

And do what Scott said, and change the field type to DECIMAL.
- 05/09/10 6:51pm
  
  Lawrence Krubner says:
  Thanks, Nathan. The field started off as DECIMAL, then I changed it for some reason having to do with how the Symfony form classes were validating it. I will have to go back and remember the original reason I went with floats, and see if I can undo that.
Last edited:
05/13/10
6:30pm
Jarret Minkler says:
From the MySQL Site

Before MySQL 5.0.3, DECIMAL comparison operations are approximate as well.

Prior to MySQL 5.0.3, DECIMAL columns store values with exact precision because they are represented as strings, but calculations on DECIMAL values are done using floating-point operations. As of 5.0.6, MySQL performs DECIMAL operations with a precision of 65 decimal digits (64 digits from 5.0.3 to 5.0.5), which should solve most common inaccuracy problems when it comes to DECIMAL columns. (If your server is from MySQL 5.0.3 or higher, but you have DECIMAL columns in tables that were created before 5.0.3, the old behavior still applies to those columns. To convert the tables to the newer DECIMAL format, dump them with mysqldump and reload them.)

Therefore, you might also want to check your version of MySql, and possibly update doctrine.