Sum() and decimals in MySQL
Posted on 2004-11-09
I need to produce a statement out of an accounting system I am making using MySQL. I need to make a total of a certain field in a result set. I would normally just use sum like so:
SELECT sum(amount) FROM tableName WHERE objID=1
However, the item I want to sum is made by doing a live percentage calculation within MySQL like so:
SELECT sum((amount * percentage / 100)) FROM tableName WHERE objID=1
My problem is that I want the sum to be done on figures rounded to two decimal places ie 1.5536 should be 1.55 so when doing the num the stray 0.0036 does not interfere with the final total.
Can I do this ?