Precision Issue with MySQL numeric fields

This is a bit of a weird one.

Before we go any further, I have tried switching the field to INT, FLOAT, DOUBLE and DECIMAL with the following results:

INT - Doesn't seem to allow me to specify 2 decimal places, keeps setting it to 0
FLOAT - Doesn't seem to allow me to specify 2 decimal places, keeps setting it to 0
DOUBLE - Doesn't seem to allow me to specify 2 decimal places, keeps setting it to 0
DECIMAL - Removes all decimal places completely. Unacceptable for currency field.

So the problem is this.

I have a table which I store customer payments. When storing the payment data, the field value is in #.##  format.  Recently, just did a bulk update for all customers in the system to give a discount off a flat amount each.  When I queried the database for a SUM of this amount instead of :

0.50 +
0.50 +
0.50 +
= 1.50

It gave
0.50 +
0.50 +
0.50 +
= 1.501837782989874

Which makes absolutely ZERO sense whatsoever, until I do a bit more research. Some people have mentioned something about switching the field type to INT at 2 decimals ( http://bytes.com/topic/mysql/answers/862239-sum-increases-precision-decimal-point ) -- which is why I tried this to no avail.  Also found a really interesting doc on precision storage issues.  

The long and short, what is a quick and dirty way to fix this.  I do not need any more than 2 decimal places stored as this is a simple currency value.
LVL 3
microvbAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johanntagleCommented:
You need to declare decimal with the number of decimals you need, i.e. decimal(10,2) allows 8 digits to the left of the decimal and 2 to the right.  

What you descibed with adding floats is expected - that type doesn't do precision math.
microvbAuthor Commented:
@johanntagle

Could you please clarify where   "decimal(10,2)"  would go.  I have already tried setting the field to all of the above combinations with the results as stated above.  The field format is currently back to it's original state which is not Float --- it is Double.

None of the field types except "DECIMAL" which is whole number only and does not support decimals (ironic), will SUM without making up magical numbers from the kingdom of la-la land.
johanntagleCommented:
DECIMAL supports decimals, you just need to declare the column properly i.e.

create table mytable (col1 decimal(10,2));
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

microvbAuthor Commented:
@johanntagle

I manually adjusted the tables using ALTER table syntax, however when I do something like

SELECT (SUM(price) * 1.13) pricewithtax FROM tablename

The result is still a similar problem. The workaround I have found to this (which seems inneficient) is as follows:

SELECT FORMAT(CEIL((SUM(price)*1.13)*100)/100,2) pricewithtax FROM tablename

Is there a more native method for ensuring that the data type remains the same -- even when calculations are being performed as the work-around seems to really make the code not so user friendly.
johanntagleCommented:
You can use the convert or the cast function see http://dev.mysql.com/doc/refman/5.1/en/cast-functions.html
microvbAuthor Commented:
@johanntagle

I appreciate the concept, however I see no real difference in type-casting versus what I am doing as a work-around.  I am looking for a more natural solution as SUM, on a field which is alleged to be limited to 2 decimal places should not convert itself to something else when doing a calculation.

Example:

Given the following Data (CSV notation for ease of use here)
++++++++++++
ID  |   PRICE   |    
----------------------
0    |    0.50     |
1    |    1.25     |
---------------------

Structure:
ID:  INT, UNIQUE AUTOINCREMENT
PRICE:  DECIMAL(10,2)

TEST SCENARIO 1
Command:
SELECT SUM(PRICE) FROM `example`;

Result:
1.75

* As Expected

TEST SCENARIO 2
Command:
SELECT SUM(PRICE) * 1.13 FROM `example`;

Result:

* As Expected

==========================================

Large data set comprising of similar data in standard currency format (no more than 2 decimals) --- ends up returning the artifact of the decimal trailing on for up to 12 digits (on the right side of the decimal point)

Do you know of a way to ensure that when you SUM() with or without an inline calculation that no artifact digits appear without convoluting the SQL script -- aka, in a natural state ?
johanntagleCommented:
hmmm multiplying two 2-decimal numbers should result in at most 4 decimal places so that is indeed odd.  Can you post some sample data I can play with?
johanntagleCommented:
FYI I cannot replicate your problem.   See below

mysql> create table test_decimal (a decimal(10,2));
Query OK, 0 rows affected (0.15 sec)

-- INSERT 10000 records
mysql> begin
    -> declare a int;
    -> declare b decimal(10,2);
    -> set a=0;
    -> set b=0.25;
    -> while a<10000 do
    -> insert into test_decimal values (b);
    -> set a=a+1;
    -> set b=b+0.25;
    -> end while;
    -> end;
    -> |

mysql> delimiter ;
mysql> call pop_decimal();
Query OK, 1 row affected (0.42 sec)

-- SHOW SAMPLE DATA
mysql> select * from test_decimal limit 10;
+------+
| a    |
+------+
| 0.25 |
| 0.50 |
| 0.75 |
| 1.00 |
| 1.25 |
| 1.50 |
| 1.75 |
| 2.00 |
| 2.25 |
| 2.50 |
+------+
10 rows in set (0.00 sec)


mysql> select sum(a) from test_decimal;
+-------------+
| sum(a)      |
+-------------+
| 12501250.00 |
+-------------+
1 row in set (0.00 sec)

mysql> select sum(a)*1.13 from test_decimal;
+---------------+
| sum(a)*1.13   |
+---------------+
| 14126412.5000 |
+---------------+
1 row in set (0.00 sec)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
microvbAuthor Commented:
@johanntagle

I too am unable to replicate the problem. I think it may be because I have stored calculated results from another table -- which in theory, switching the field type to DECIMAL(10,2) should truncate.  I am unable to reproduce on a fresh table -- if there were some way to check for the data stored that trails beyond the field type setting of 2 decimals, that would be great :)

Just being thorough for future users looking for a solution to correct the problem with an existing table, the following command will adjust the field correctly.

ALTER TABLE `tablename` CHANGE `fieldname` `fieldname` DECIMAL(10,2);

Open in new window


Thank you for your assistance. If you can think of a way to query for all records where the decimal fraction trails beyond 2 places, please add that here as it would be very helpful in locating this issue -- as it is rather obscure especially when automating and rounding SUM totals with calculations, this problem is all but invisible without deeper inspection of the data being returned.
microvbAuthor Commented:
I just noticed that you are experiencing something similar where the result being returned is NOT the same data type as you are SUM'ming on.

mysql> select sum(a)*1.13 from test_decimal;
+---------------+
| sum(a)*1.13   |
+---------------+
| 14126412.5000 |
+---------------+

Open in new window


Note that it returns 4 decimal positions when the field is actually 2 decimal points. Further, it appears to be 0 padded. Where data stored in the DECIMAL field is actually rounded up to the nearest (2) decimals as suggested by DECIMAL(10,2), the resulting SUM ignores this and appears to auto-cast into a different data type with padding.
johanntagleCommented:
That is because we multiplied two numbers that have two decimals each.  The resulting product expectedly has 4, even if they are all zeroes.  That's just the way it is.  when you store te result to a decimal(10,2) it will get truncated.  If you need the product to show only two decimals upon computation, then just use round() or truncate() functions.  See http://dev.mysql.com/doc/refman/5.1/en/mathematical-functions.html
microvbAuthor Commented:
My comment contains information relating to altering existing tables.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.