Wrong sum with float and group by

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.

_mario.lat wrote:
> 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.

Mario,

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

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

>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.

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.

On Tue, 07 Nov 2006 06:40:48 -0500, Jerry Stuckle wrote:

> _mario.lat wrote:
>> Hallo,
>> I have a problem with a query like that:
>> 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.

_mario.lat wrote:

> On Tue, 07 Nov 2006 06:40:48 -0500, Jerry Stuckle wrote:
>
>> _mario.lat wrote:
>>> Hallo,
>>> I have a problem with a query like that:
>>> 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


4.7 actually.


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




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

_mario.lat wrote:
> On Tue, 07 Nov 2006 06:40:48 -0500, Jerry Stuckle wrote:
>
>
>>_mario.lat wrote:
>>
>>>Hallo,
>>>I have a problem with a query like that:
>>>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.

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?

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