SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-28-2008, 08:30 AM
| |||
| |||
 Re: Wrong sum with float and group by _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 ==================      |
|
02-28-2008, 08:30 AM
| |||
| |||
| Re: Wrong sum with float and group by >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. |
|
02-28-2008, 08:31 AM
| |||
| |||
| Re: Wrong sum with float and group by 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. |
|
02-28-2008, 08:31 AM
| |||
| |||
| Re: Wrong sum with float and group by _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 |
|
02-28-2008, 08:31 AM
| ||||
| ||||
| Re: Wrong sum with float and group by _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 ================== |
Linear Mode
