"...the mind soon boggles at the variety and intricacies of the rounding algorithms that may be used for different applications" - Clive Maxfield
You don't have to be a mathematician to realize that, when it comes to rounding, there are a lot of ways to go about it. DBAs and programmers are often faced with the unenviable task of choosing amongst a plethora of rounding algorithms that will work best within a particular context. The choice becomes especially crucial in the area of finances. Although MySQL does provide its own Round() function, for those times that it doesn't provide the results that you're looking for, you'll be happy to know that implementing your own rounding function is not all that difficult. I'll show you a few to get you started here today.
MySQL's Native Round() Function
I described MySQL's Round() function in my Three Handy MySQL Numerical Functions article. To recap:
The MySQL Round() function uses Half Away From ZERO rounding. That's similar to the kind that you learned back in grade school, but including negative numbers. Remember that 5 is rounded up because once you're half way there, you might as well continue in the same direction. In Half Away From ZERO rounding, negative numbers work in the same way, so that they tend to move away from zero, so that:
- 7.5 or 7.6 round up to 8
- 7.4 rounds down to 7
- -7.4 rounds to -7
- -7.5 or -7.6 round away from zero to -8
Making Use of Built-in Functions
MySQL has other functions that can assist you in your rounding endeavors. Three of the best include Ceiling(), Floor(), and Truncate().
Ceiling() - alias Ceil() - always rounds the number up to the nearest integer. Unfortunately, there is no argument for precision so it can only return whole numbers when used by itself:
SELECT CEILING( 1.4 ); -- returns 2
SELECT CEILING( 1.6 ); -- also returns 2
SELECT CEILING( -1.4 ); -- returns -1
SELECT CEILING( -1.6 ); -- also returns -1
Floor() is the counterpart of Ceiling() and always rounds the number down to the nearest integer, so that:
SELECT FLOOR( 1.4 ); -- returns 1
SELECT FLOOR( 1.6 ); -- also returns 1
SELECT FLOOR( -1.4 ); -- returns -2
SELECT FLOOR( -1.6 ); -- also returns -2
Truncate() removes digits after the specified number of decimal places. The function takes two arguments: the number to be truncated and the number of decimal places to truncate to. If the decimal places argument is a negative number then the digits to the left of the decimal place are replaced with zeros.
Here are some examples:
SELECT TRUNCATE( 1.5555, 0 ); -- returns 1
SELECT TRUNCATE( 1.5555, 1 ); -- returns 1.5
SELECT TRUNCATE( -1.5555, 0 ); -- returns -1
SELECT TRUNCATE( -1.5555, 1 ); -- returns -1.5
SELECT TRUNCATE( 12345, -1 ); -- returns 12340
SELECT TRUNCATE( 12345, -2 ); -- returns 12300
SELECT TRUNCATE( 12345, -3 ); -- returns 12000
Implementing a Round_Up() Function
The results of the Round-Up rounding algorithm vary depending on the exact meaning of "up". Some people understand "up" to refer to round on the positive side, which is synonymous with round-ceiling. The "up" may also mean to head away from zero; in this case, round-up acts more in accordance with the round-away-from-zero algorithm.
Applying round-ceiling to a negative number has the effect of dropping all non-zero digits after the desired precision. Hence, -2.789 rounded to a whole number becomes 2, and -100.333333 rounded to three places after the decimal is truncated to -100.333. In both cases, all digits following significant ones are dropped.
Dropping digits of precision might sound familiar. That's because the Truncate() function introduced above does just that. Here it is inside a user function called ROUND_CEILING. It accepts a number as well as the number of desired degrees of precision. A very large DECIMAL(32, 16) data type allows for the greatest flexibility and precision when working with numeric data:
CREATE FUNCTION ROUND_CEILING(num DECIMAL(32,16), places INT) RETURNS DECIMAL(32,16) DETERMINISTIC RETURN CASE WHEN num < 0 THEN truncate(num, places) ELSE ceil(ABS(num) * power(10, places)) / power(10, places) END;
Here are some results, first with positive numbers, then with negative ones:
FUNCTION CALL RETURN VALUE SELECT ROUND_CEILING(19456.4567, 0) 19457.0000000000000000 SELECT ROUND_CEILING(22.22222222, 2) 22.2300000000000000 SELECT ROUND_CEILING(100.9999999, -2) 200.0000000000000000 SELECT ROUND_CEILING(-100.9999999, 2) -100.9900000000000000 SELECT ROUND_CEILING(-999.123456789, -2) -900.0000000000000000
Rounding up towards positive infinity takes a different approach with negative numbers than the ROUND_CEILING() function. Here, our goal is to perform exactly the same operation as on positive numbers, except in reverse. That necessitates converting the num argument to its equivalent positive value and then re-adding the negative symbol to the final result. Our second function is called ROUND_UP() and accepts exactly the same parameters as ROUND_CEILING():
CREATE FUNCTION ROUND_UP(num DECIMAL(32,16), places INT) RETURNS DECIMAL(32,16) DETERMINISTIC RETURN CASE WHEN num < 0 THEN - ceil(abs(num) * power(10, places)) / power(10, places) ELSE ceil(abs(num) * power(10, places)) / power(10, places) END;
Here again are some results. Pay particular attention to negative numbers, which have been rounded upwards:
FUNCTION CALL RETURN VALUE SELECT ROUND_UP(19456.4567, 0) 19457.0000000000000000 SELECT ROUND_UP(22.22222222, 2) 22.2300000000000000 SELECT ROUND_UP(100.9999999, -2) 200.0000000000000000 SELECT ROUND_UP(-123.123, 1) -123.1000000000000000 SELECT ROUND_UP(-100.9999999, -2) -200.0000000000000000
Trimming Our Results Down to Size
Once you obtain your results from the above functions, you'll likely want to store them in a smaller data type. Assuming that you now have exactly the degree of precision that you're after, you can safely truncate all non-essential digits (zeros) from your number. Again, the Truncate() function is there to help:
SELECT TRUNCATE(-123.1000000000000000, 1) -123.1
The FLOOR() function can also be employed to produce whole numbers:
SELECT FLOOR(200.0000000000000000) 200
Looking over various MySQL forums certainly reinforces the notion that a lot of DBAs and developers are in need of a variety of rounding methods. The functions that we saw here today, while rudimentary, are at least a step towards having access to other rounding approaches than the standard round-away-from-zero one.