Round sum of decimal columns in sql

Question

I am using this code for sum/addition and to multiply two values of database AMOUNT & RATE.

SELECT YEAR, COST, RATE, SUM(AMOUNT) AS ONE, ROUND(SUM(AMOUNT*RATE/100),0) AS COST 
FROM DATABASE 
WHERE YEAR='$YEAR'

But round value is not working

650 x 5 / 100 = 32  instead of 33
720 x 8 / 100 = 57  instead of 58

Total of COST is coming to 89 instead of 91 as i need total of round value

Does this answer your question? [MySQL - How can I always round up decimals?](https://stackoverflow.com/questions/7705548/mysql-how-can-i-always-round-up-decimals) — ADyson, Feb 14 '21 at 19:39
`SELECT ROUND(650*5/100,0);` returns 33. Cannot reproduce your problem. — simon.ro, Feb 14 '21 at 19:54

sticky bit · Accepted Answer · 2021-02-14T20:18:51.933

You currently round the sum, not the summands. Round the summands. (And use ceil() instead of round(), if you always want the minimal greater or equal integer, not "true" rounding.)

SELECT year,
       cost,
       rate,
       sum(amount) one,
       sum(round(amount * rate / 100, 0)) cost 
       FROM `database`
       WHERE year = ?
       GROUP BY year,
                cost,
                rate;

Some notes:

Your query was malformed in general. There were columns in the list of selected columns that neither were in the GROUP BY clause nor an argument to an aggregation function. Sadly old MySQL versions or badly configured ones accept such erroneous queries. But the result can suddenly be funny.
Do not use string interpolation or concatenation to get values into SQL queries. That's error prone and might make your program vulnerable to SQL injection attacks. Use parameterized queries. See "How to include a PHP variable inside a MySQL statement" and "How can I prevent SQL injection in PHP?".
database is a really bad table name.

Round sum of decimal columns in sql

1 Answers1