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.

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.

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.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

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.

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.

create table mytable (col1 decimal(10,2));

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.

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.

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 ?

```
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)
```

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 trialI 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);
```

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.

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

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.

MySQL Server

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

What you descibed with adding floats is expected - that type doesn't do precision math.