Understanding the Concept of Rounding to the Nearest Cent

When a number is rounded off without a specified number of decimal places, it is rounded off to the closest integer. However, there is an alternative solution which involves using the ROUND() function in MySQL, which can be used to round a number to a particular number of decimal places.


Solution 1:

In case you require the amount rounded off to the closest 5 cents, you may utilize a method similar to this one.

create table t (val decimal(9,4));
insert into t values
 (31.20),(31.21),(31.22),(31.23),(31.24)
,(31.25),(31.26),(31.27),(31.28),(31.29);
select 
    val
  , Nearest5 = round(val/.05,0)*.05
  -- , Nearest5 = round(val*20,0)/20 -- alternate way of writing the expression
from t

Check out the demonstration of rextester at this link: http://rextester.com/UUNA22566.

returns:

+---------+----------+
|   val | Nearest5 |
+-------+----------+
| 31.20 | 31.20    |
| 31.21 | 31.20    |
| 31.22 | 31.20    |
| 31.23 | 31.25    |
| 31.24 | 31.25    |
| 31.25 | 31.25    |
| 31.26 | 31.25    |
| 31.27 | 31.25    |
| 31.28 | 31.30    |
| 31.29 | 31.30    |
+-------+----------+


Solution 2:


One possible solution is to enclose the computation in a designated

ROUND

procedure.

ROUND(GrossPremium * (CompanyCommissionActual /100), 2) as GrossCommission,
...
ROUND((GrossPremium -  (GrossPremium * (CompanyCommissionActual / 100))) + PayableFees, 2) as NetDueCarrier

Assuming that you are utilizing SQL Server with a version that is equal to or greater than 2008, additional details can be found here.

Frequently Asked Questions