Rounding Up in SQL

Working in the telecommunications industry, you run into situations not always found in the real world.  For example, when billing a call or applying taxes to an account, you always round up.  We can't charge a customer anything less than 1¢ (since they can't pay less than 1¢), so you have to round up.

SQL has a function that allows you to perform rounding on your values.  This function is called ROUND(), but only does true rounding (below 5 = round down, above 5 = round up).  So using this function, how do we always round up?

First, we have to figure out how many decimal places we will be billing our customer.  Let's say, for example, that our customer's calls are rounded to the nearest two decimals.  Once we know how many decimals we need to round, we can apply our logic.  The logic is then to add 49 one digit past the decimal place you are trying to round.  In this case, since we want to round to the nearest two decimal places, we will add $0.0049 to our value and then round.  If we wanted to go three decimal places, we would add $0.00049, $0.000049 to four decimal places, etc.

The following will round to $0.01:

select ROUND(0.013, 2)

But if we want to always round up, we need to add $0.0049:

select ROUND(0.013+0.0049, 2)

The above rounds to $0.02.

Now, you might be asking yourself: why do we add $0.0049 instead of $0.005?  Well, adding $0.005 might inadvertently makes us round higher than we want to go.  Look below for an example, which will round up to $0.03:

select ROUND(0.020+0.005, 2)

When we actually want it to stay at $0.02:

select ROUND(0.020+0.0049, 2)

I've found it is more productive to add $0.0049 to the number and then round, as opposed to trying to figure out a way for SQL to round up only those values that need it.

To read up more on rounding, go here: SQL ROUNDING