Wrong sum with float and group by - MySQL

[_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]

_mario.lat wrote: 

Mario,

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

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
net
==================

[Gordon]

>I have a problem with a query like that: 

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.

[_mario.lat]

On Tue, 07 Nov 2006 06:40:48 -0500, Jerry Stuckle wrote:
 
>
> Mario,
>
> What do you mean "total is not the exact sum"?[/ref]

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]

_mario.lat wrote:
 
>>
>> Mario,
>>
>> What do you mean "total is not the exact sum"?[/ref]
>
> 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[/ref]


4.7 actually.

 


I can't te 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)




--
Brian Wakem
Email: http://homepage.ntlworld.com/b.wakem/myemail.png

[Jerry]

_mario.lat wrote: 
>>
>>Mario,
>>
>>What do you mean "total is not the exact sum"?[/ref]
>
>
> 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.[/ref]

Mario,

Hmmm, I agree with Brian. I can't te 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?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
net
==================