MySQL, Issue in ROUND function with a calculation inside

Question

I have a table called "itemprices" and there is a field called "price". I need to query the price value with it's GST value which is 10% of the price value, and more importantly that value must be rounded to two decimals.

If the price = 70.85, GST value = 7.085 (price / 100 * 10);

A) When I use ROUND function with the above calculation, it will return 7.08
B) When I use ROUND function with 7.085 directly, it will return 7.09

(Actually I would like to have 7.09 in both situations)

See below query.

SELECT 
price,
(price / 100 * 10) AS gstvalue, 
ROUND( (price / 100 * 10), 2 ) AS roundedgstvalue, 
ROUND( 7.085, 2 ) AS customround FROM  `itemprices` 
WHERE id = 10

Returned values are as below.

price = 70.85
gstvalue = 7.085
roundedgstvalue = 7.08
customround = 7.09

I'm using MySQL version 5.1.41 and I would like to know why the ROUND function is behaving differently in the situations A and B.

Also I would like to have a solution to get the value 7.09 for the situation B.

Appreciate your comments and answers. Thank you!

@dirtytyphon, both are same, aren't they? but I tried with (price * 0.1) also, but the result is still same. — asankasri, Apr 10 '15 at 06:24
https://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_round — , Apr 10 '15 at 06:26
@dirtytyphon, yeah I have followed the doc already before posting this question. But still I can't imagine the reason for my issue and how to solve it. — asankasri, Apr 10 '15 at 06:33
You should be able to incorporate ceil or floor functions to achieve what you need. just check this thread http://stackoverflow.com/questions/7705548/mysql-how-can-i-always-round-up-decimals — Yasitha Waduge, Apr 10 '15 at 06:40
@YasithaChinthaka, I thought about that before. But your answer encouraged me to try for that. I did it as below, hopefully it will work. `ROUND((CEIL (price * 10) / 100),2) AS roundedgstvalue` — asankasri, Apr 10 '15 at 07:10

asankasri · Accepted Answer · 2015-04-14T08:35:47.800

ROUND( (
CEIL( price *10 ) /100 ) , 2
) AS roundedgstvalue

Above solved my issue.

PS ::: Even the above soloved my issue for the value 70.85, it was not correct for the values such as 70.83. Because, still it was returning 7.09 as the roundedgstvalue. So that I had to do some check in my solution.

IF( price >= ( (
(
FLOOR( price *10 ) + CEIL( price *10 ) ) /2 ) /10
), ROUND( (
CEIL( price *10 ) /100 ) , 2
), ROUND( (
FLOOR( price *10 ) /100 ) , 2
)
) AS roundedgstvalue

MySQL, Issue in ROUND function with a calculation inside

1 Answers1