Sum() and decimals in MySQL

Posted on 2004-11-09
Last Modified: 2012-06-21

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

    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

    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.


    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    I have been using r1soft Continuous Data Protection ( 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 ( 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 (, 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…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now