Mysql - round half down or half up

Question

Is there a way to specify if we want to round a field value 4.485 to 4.48(half down) or to 4.49 (half up) in a sql query with MySql?

Thanks

Answer 1

Even if the question is years old, I had the same problem and found a different solution I'd like to share.

Here it is: using a conditional function you explicitly decide which way you want to round when the last decimal is 5. For instance, if you want to round half up with 2 decimals:

SELECT IF ((TRUNCATE(mynumber*1000,0) mod 5) = 0,CEIL(mynumber*100)/100,ROUND(mynumber,2)) as value FROM mytable

You can use FLOOR function instead of CEIL if you want to round half down; if you want a different number of decimals, say n, you change the multiplier inside the TRUNCATE call (10^n+1) and you pass it to the ROUND function.

SELECT IF ((TRUNCATE(mynumber*([10^*n+1*]),0) mod 5) = 0,[CEIL|FLOOR](mynumber*100)/100,ROUND(mynumber,[*n*])) as value FROM mytable

Answer 2

Based on the official MySQL documentation there is no way to specify a rounding strategy. By default, ROUND uses the “round half up” rule for exact-value numbers. However, I needed a function that behaves like java.math.BigDecimal with java.math.RoundingMode.HALF_DOWN mode. So the only solution that I found was to create my own function.

The code (tested in MySQL 5.7)

DELIMITER //
DROP FUNCTION IF EXISTS roundHalfDown //
CREATE FUNCTION roundHalfDown (
    numberToRound DECIMAL(25,15),
    roundingPrecision TINYINT(2)
)
    RETURNS DECIMAL(25,15)
BEGIN
    DECLARE digitPosition TINYINT (2) UNSIGNED DEFAULT 0;
    DECLARE digitToRound TINYINT (2) DEFAULT -1;
    DECLARE roundedNumber DECIMAL(20,6) DEFAULT 0;

    SET digitPosition = INSTR(numberToRound, '.');

    IF (roundingPrecision < 0) THEN
        SET digitPosition = digitPosition + roundingPrecision;
    ELSE
        SET digitPosition = digitPosition + roundingPrecision + 1;
    END IF;

    IF (digitPosition > 0
        AND digitPosition <= CHAR_LENGTH(numberToRound)
    ) THEN
        
        SET digitToRound = CAST(
                SUBSTR(
                numberToRound, 
                digitPosition, 
                1
            ) AS UNSIGNED
        );

        SET digitPosition = digitPosition - 1;
    END IF;

    IF (digitToRound > -1) THEN

        IF (digitToRound > 5) THEN

            SET roundedNumber = ROUND(numberToRound, roundingPrecision);
        ELSEIF (digitToRound = 5 AND CAST(
                SUBSTR(
                REPLACE(numberToRound, '.', ''), 
                digitPosition + 1
            ) AS UNSIGNED) > 0) THEN
            SET roundedNumber = ROUND(numberToRound, roundingPrecision);
        ELSE
            SET roundedNumber = TRUNCATE(numberToRound, roundingPrecision);
        END IF;

    ELSEIF (roundingPrecision > 0) THEN
        SET roundedNumber = numberToRound;
    END IF;

    RETURN roundedNumber;
END //
DELIMITER ;

Test Cases

SELECT roundHalfDown(1.541, 2); #1.54
SELECT roundHalfDown(1.545, 2); #1.54
SELECT roundHalfDown(1.5451, 2); #1.55
SELECT roundHalfDown(1.54500001, 2); #1.55
SELECT roundHalfDown(1.54499999, 2); #1.54
SELECT roundHalfDown(-1.545, 2); #-1.54
SELECT roundHalfDown(-1.5451, 2); #-1.55
SELECT roundHalfDown(555, 0); #555
SELECT roundHalfDown(1000999, -1); #1001000
SELECT roundHalfDown(1000999, -2); #1001000
SELECT roundHalfDown(1000999, -3); #1001000
SELECT roundHalfDown(1000999, -4); #1000000

I used this answer from another Stack Overflow question, but I changed it a bit for my specific case.

Answer 3

Andrea Pegoretti's answer is almost correct, however it will apply when it's a 5 or a 0 instead of just a 5.

My fix (for round down):

SELECT IF (SUBSTR(TRUNCATE(CEIL(mynumber*1000),0),-1) = 5,FLOOR(mynumber*100)/100,ROUND(mynumber, 2))) AS value FROM mytable

Answer 4

The accepted answer of this post offers a good solution : MySQL - How can I always round up decimals?

i.e multiply by 10^n where n is the decimal place you want to round to then call ceil to round up or floor to round down and divide by 10^n.

Answer 5

You can use the CEIL (or CEILING) and the FLOOR functions to round up/round down.

For example:

CEIL(4.485 * 100) / 100 will return 4.49
FLOOR(4.485 * 100) / 100 will return 4.48

Answer 6

I have the same question and I try all kind of custom function to emulate the functionality of PHP with a round up and down, but after a while, I figured out that MySQL produces different results using float and doubles fields.

Here my test.

mysql> describe test_redondeo;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| columna_double | double(15,3) | YES  |     | NULL    |       |
| columna_float  | float(9,3)   | YES  |     | NULL    |       |
| id             | int(11)      | NO   | PRI | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

In fields type double, round function truncate, but in float round up.

mysql> select columna_double, columna_float, round ( columna_double, 2) as round_double, round ( columna_float, 2) as round_float from test_redondeo;
+----------------+---------------+--------------+-------------+
| columna_double | columna_float | round_double | round_float |
+----------------+---------------+--------------+-------------+
|        902.025 |       902.025 |       902.02 |      902.03 |
+----------------+---------------+--------------+-------------+
1 row in set (0.00 sec)

If you made a custom function for round a number using round() in any way, please pay attention to type of field.

Hope this test helps you.

Answer 7

According to Mysql docs,

• For exact-value numbers, ROUND() uses the “round half up” rule

I had also a situation, where I was wondering why is Mysql rounding(1) 74,447 to 74,4. It was because it was in fact an endless long decimal, a approximate-value number, and not a exact-value number. The solution for me was, that I had to use ROUND() 3x like this: ROUND(ROUND(ROUND(value, 3), 2), 1). The first rounding makes sure that it's not a approximate-value number any more (where Mysql uses the “round to nearest even” rule), but a exact-value number, a decimal 3 number.