Solved

# Sum() and decimals in MySQL

Posted on 2004-11-09
2,821 Views
Hi

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 ?
0
Question by:rfila

LVL 15

Accepted Solution

ROUND(number, dec_places) will round number to the specified number of decimal places. So for example,

SELECT ROUND(10.554, 2);   ----> 10.55

so I think you could do something like:

SELECT sum((ROUND(amount,2) * ROUND(percentage,2) / 100));

Cheers,
Matt
0

LVL 12

Expert Comment

I am not too sure about the last part of your question, but shouldn't you only round after sum? Isn't it more accurate to round off at the final figure then rounding yoour calculating figures, it will lose accuracy.

SELECT ROUND(SUM(amount * percentage / 100),2) FROM tableName WHERE objID=1
0

Author Comment

In the end I did this:

SUM(round((amount * percentage / 100), 2))

The sum is adding up the rounded off result for each row.  Therefore, when I print the result set with the rounded off figures if I add up the numbers they will come to the same amound as the result of this SQL request.  I was finding that they were slightly different sometimes because the fractions of pennys were cumulating.

Thanks!
0

## Featured Post

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…