_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
==================
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....
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:
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
==================
>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.
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.
_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
_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
==================
Bookmarks