Wrong sum with float and group by

[_mario.lat]

Hallo,
I have a problem with a query like that:
SEMECT SUM(price) as total FROM product GROUP BY type

price is float or double.
total is not the exact sum.
How can I do?
Thank you in advance,
Mario.

[Jerry Stuckle]

Post by _mario.lat
Hallo,
SEMECT SUM(price) as total FROM product GROUP BY type
price is float or double.
total is not the exact sum.
How can I do?
Thank you in advance,
Mario.

Mario,

What do you mean "total is not the exact sum"?

[_mario.lat]

Post by Jerry Stuckle

Post by _mario.lat
Hallo,
SELECT SUM(price) as total FROM product GROUP BY type
price is float or double.
total is not the exact sum.
How can I do?
Thank you in advance,
Mario.

Mario,
What do you mean "total is not the exact sum"?

Thankyou to all for answering (and reading) me.

If you have a table with the value:
type,price
a,1.2
b,0.1
c,3.4

real SUM (or real total) is 1.2+0.1+3.4 = 4.6
the query
SELECT SUM(price) as total FROM product GROUP BY type
give me: total 4.5 for example.

Thankyou again,
Mario.

[Brian Wakem]

Post by _mario.lat

Post by Jerry Stuckle

Post by _mario.lat
Hallo,
SELECT SUM(price) as total FROM product GROUP BY type
price is float or double.
total is not the exact sum.
How can I do?
Thank you in advance,
Mario.

Mario,
What do you mean "total is not the exact sum"?

Thankyou to all for answering (and reading) me.
type,price
a,1.2
b,0.1
c,3.4
real SUM (or real total) is 1.2+0.1+3.4 = 4.6

4.7 actually.

Post by _mario.lat
the query
SELECT SUM(price) as total FROM product GROUP BY type
give me: total 4.5 for example.

I can't replicate that. I suspect you aren't telling us the full story.



mysql> SELECT * FROM math;
+----+-------+------+
| id | price | type |
+----+-------+------+
| 1 | 1.2 | 1 |
| 2 | 0.1 | 1 |
| 3 | 3.4 | 1 |
+----+-------+------+
3 rows in set (0.00 sec)

mysql> SELECT SUM(price) AS total FROM math GROUP BY type;
+------------------+
| total |
+------------------+
| 4.70000014454126 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT FORMAT(SUM(price),1) AS total FROM math GROUP BY type;
+-------+
| total |
+-------+
| 4.7 |
+-------+
1 row in set (0.00 sec)

[Jerry Stuckle]

Post by _mario.lat

Post by Jerry Stuckle

Post by _mario.lat
Hallo,
SELECT SUM(price) as total FROM product GROUP BY type
price is float or double.
total is not the exact sum.
How can I do?
Thank you in advance,
Mario.

Mario,
What do you mean "total is not the exact sum"?

Thankyou to all for answering (and reading) me.
type,price
a,1.2
b,0.1
c,3.4
real SUM (or real total) is 1.2+0.1+3.4 = 4.6
the query
SELECT SUM(price) as total FROM product GROUP BY type
give me: total 4.5 for example.
Thankyou again,
Mario.

Mario,

Hmmm, I agree with Brian. I can't replicate your problem either. You
will have a slight error due to floating point storage format as
indicated by Gordon, but it should be quite a bit smaller than what you
show.

What do you get if you do a SELECT type, total FROM product?

[Gordon Burditt]

Post by _mario.lat
SEMECT SUM(price) as total FROM product GROUP BY type
price is float or double.
total is not the exact sum.

There is no exact representation in binary floating point
for most decimal numbers that are not exact integers.

Fixes (none particularly satisfactory):

- Use a decimal type that MySQL doesn't use floating point calculations on.
(Not sure if there are any, but the DECIMAL types are good to try)
- Represent currency as an integer number of cents or other smallest unit
in a float, double, or 64-bit integer.
- Explicitly round the sum (which might make things worse)
- Live with it.