MySQL round half

Question

Is it possible, in MySQL, to round half a specific way like PHP would do?

PHP_ROUND_HALF_UP
PHP_ROUND_HALF_DOWN
PHP_ROUND_HALF_EVEN
PHP_ROUND_HALF_ODD

Or are we really limited to only rounding up?

score 5 · Accepted Answer · edited May 23 '17 at 12:23

1. From Oracle documentation:

Based on MySQL official documentation, the rounding function act as the following:

For exact-value numbers, ROUND() uses the “round half away from zero” or “round toward nearest” rule: A value with a fractional part of .5 or greater is rounded up to the next integer if positive or down to the next integer if negative. (In other words, it is rounded away from zero.) A value with a fractional part less than .5 is rounded down to the next integer if positive or up to the next integer if negative.

http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html#function_round

which means that we can only use the rounding function to round up. I have written the following UDF to work around this limitation.

2. The code (tested in MySQL 5.6):

CREATE FUNCTION roundHalf (
    numberToRound DECIMAL(20,6),
    roundingPrecision TINYINT(2),
    roundingType ENUM (
        'ROUND_HALF_UP',
        'ROUND_HALF_DOWN',
        'ROUND_HALF_EVEN',
        'ROUND_HALF_ODD'
    )
)
    RETURNS DECIMAL(20,6)
BEGIN
    DECLARE digitEvenOdd TINYINT (2) UNSIGNED DEFAULT 255;
    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 < 1) 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;

        IF (digitPosition > 0 AND 
            digitPosition <= CHAR_LENGTH(numberToRound)
        ) THEN    

            SET digitEvenOdd = CAST(
                SUBSTR(
                    numberToRound, 
                    digitPosition, 
                        1
                ) AS UNSIGNED
            );
        END IF;
    END IF;

    IF (digitToRound > -1) THEN

        CASE roundingType

            WHEN 'ROUND_HALF_UP' THEN

                IF (digitToRound >= 5) THEN

                    SET roundedNumber = ROUND(numberToRound, roundingPrecision);
                ELSE
                    SET roundedNumber = TRUNCATE(numberToRound, roundingPrecision);
                END IF;

            WHEN 'ROUND_HALF_DOWN' THEN

                IF (digitToRound > 5) THEN

                    SET roundedNumber = ROUND(numberToRound, roundingPrecision);
                ELSE

                    SET roundedNumber = TRUNCATE(numberToRound, roundingPrecision);
                END IF;

            WHEN 'ROUND_HALF_EVEN' THEN

                IF (digitToRound >= 5 AND 
                    digitEvenOdd IN (1,3,5,7,9)
                ) THEN

                    SET roundedNumber = ROUND(numberToRound, roundingPrecision);
                ELSE

                    SET roundedNumber = TRUNCATE(numberToRound, roundingPrecision);
                END IF;

            WHEN 'ROUND_HALF_ODD' THEN

                IF (digitToRound >= 5 AND
                    digitEvenOdd IN (0,2,4,6,8)
                ) THEN

                    SET roundedNumber = ROUND(numberToRound, roundingPrecision);
                ELSE

                    SET roundedNumber = TRUNCATE(numberToRound, roundingPrecision);
                END IF;
        END CASE;

    ELSEIF (roundingPrecision > 0) THEN

        SET roundedNumber = numberToRound;
    END IF;

    RETURN roundedNumber;
END //

Note: The value the number to round can be increased based on the need of your project up to 65 digits in total (in that case, do not forget to change all the instances of DECIMAL(20,6) accordingly).

https://stackoverflow.com/a/19201329/4949388

3. Rounding results in PHP:

http://sandbox.onlinephpfunctions.com/code/054de06b074c2b3ece5fb6e5d4180524cd2207e2

4. Unit tests (in SQL):

/* round number not enough digits */

IF (roundHalf(1.455, 7, 'ROUND_HALF_UP') <> 1.455) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_ODD_TEST_1';

ELSEIF (roundHalf(1.455, -5, 'ROUND_HALF_UP') <> 0) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_ODD_TEST_2';

ELSEIF (roundHalf(555, -1, 'ROUND_HALF_UP') <> 560) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_ODD_TEST_3';

END IF;


/* round half up */

IF (roundHalf(1.541, 2, 'ROUND_HALF_UP') <> 1.54) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_UP_TEST_1';

ELSEIF (roundHalf(1.545, 2, 'ROUND_HALF_UP') <> 1.55) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_UP_TEST_2';

ELSEIF (roundHalf(555, 0, 'ROUND_HALF_UP') <> 555) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_UP_TEST_3';

ELSEIF (roundHalf(1000999, -2, 'ROUND_HALF_UP') <> 1001000) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_UP_TEST_4';

ELSEIF (roundHalf(1000999, -3, 'ROUND_HALF_UP') <> 1001000) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_UP_TEST_5';

ELSEIF (roundHalf(1000999, -4, 'ROUND_HALF_UP') <> 1000000) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_UP_TEST_6';
END IF;


/* round half down */

IF (roundHalf(1.541, 2, 'ROUND_HALF_DOWN') <> 1.54) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_DOWN_TEST_1';

ELSEIF (roundHalf(1.545, 2, 'ROUND_HALF_DOWN') <> 1.54) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_DOWN_TEST_2';

ELSEIF (roundHalf(555, 0, 'ROUND_HALF_DOWN') <> 555) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_DOWN_TEST_3';

ELSEIF (roundHalf(1000999, -2, 'ROUND_HALF_DOWN') <> 1001000) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_UP_TEST_4';

ELSEIF (roundHalf(1000999, -3, 'ROUND_HALF_DOWN') <> 1001000) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_DOWN_TEST_5';

ELSEIF (roundHalf(1000999, -4, 'ROUND_HALF_DOWN') <> 1000000) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_DOWN_TEST_6';
END IF;


/* round half even */

IF (roundHalf(1.541, 2, 'ROUND_HALF_EVEN') <> 1.54) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_EVEN_TEST_1';

ELSEIF (roundHalf(1.544, 2, 'ROUND_HALF_EVEN') <> 1.54) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_EVEN_TEST_2';

ELSEIF (roundHalf(1.455, 2, 'ROUND_HALF_EVEN') <> 1.46) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_EVEN_TEST_3';
END IF;

/* round half odd */

IF (roundHalf(1.544, 2, 'ROUND_HALF_ODD') <> 1.54) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_ODD_TEST_1';

ELSEIF (roundHalf(1.545, 2, 'ROUND_HALF_ODD') <> 1.55) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_ODD_TEST_2';

ELSEIF (roundHalf(1.455, 2, 'ROUND_HALF_ODD') <> 1.45) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_ODD_TEST_3';
END IF;

Use that coding as you please, but don't forget to like my post. Thank you all for your comments and suggestions.

Thanks for the creating this MySQL function and with detailed description. But during testing I found one issue for the ROUND_HALF_EVEN case, You are checking this condition( IF (digitToRound >= 5 AND digitEvenOdd IN (1,3,5,7,9) ) THEN) to round up but consider the value 2.187 with rounding value 2 then using this method it should round up to 2.19 but it is returning 2.18. I think it should be as IF (digitToRound > 5 OR (digitToRound = 5 AND digitEvenOdd IN (1,3,5,7,9)) ) — mksmanjit, Jul 05 '21 at 09:46

score 3 · Answer 2 · answered Apr 25 '18 at 19:50

There has been a bug in the ROUND() function in MySQL for quite some time where it returns inconsistent results depending on whether it considers the input value it is rounding an exact or approximate number. There are at least a couple of bugs logged on this (see here as well as MySQL 5.7 description of the behavour here).

I experienced the issue with MySQL 5.6.22 but it appears to be quite pervasive judging by some of the discussion dating back to 2001. It appears to have been fixed in late 2017 early 2018 but I cannot attest as to which versions work correctly and which don't.

The following is a workaround that will allow you to round to one decimal place with consistent results regardless whether <input_value> is treated by MySQL as an exact or approximate number:

SELECT ROUND(CAST(<input_value> AS DECIMAL(10,2)), 1)

If you need to round to more round places, change the second parameter to ROUND() from 1 to the number of decimal places you need and the second parameter to DECIMAL() from 2 to a value one higher than the number of decimal places you need. E.g. for 3 decimal places you could use:

SELECT ROUND(CAST(<input_value> AS DECIMAL(10,4)), 3)

Note that the first parameter for DECIMAL() is the total number of digits in the number so, if you need to store large values, you may need to adjust that accordingly. For more details see the MySQL manual.

score 2 · Answer 3 · answered Apr 25 '16 at 15:29

2

Rounding on half specifically can be done using the ROUND function in just a single line:

SELECT ROUND(ROUND({your_input_value}*2,0)/2,1);

The first ROUND({your_input_value}*2,0) gives you a rounded value as a whole integer.
The second ROUND(.../2,1) gives you a single digit result instead of a bit longer result with more zeros and possible floating point deviations.

Similar other rounds with round up, down, or other multipliers like 3,4, etc can be created.

answered Apr 25 '16 at 15:29

Norbert

6,026
3
17
40

1

Your code seems to always round down. In my algorithm I'm using the truncate function to do that: `TRUNCATE(numberToRound, roundingPrecision)`. In that case, my method seems a little bit easier/shorter than yours. Moreover, my algorithm offers all the four rounding mode asked in the initial question. Thank you anyway for the effort. – Jonathan Parent Lévesque Apr 25 '16 at 16:02

score 1 · Answer 4 · answered Dec 11 '18 at 20:10

If you get an error applying @Jonathan's function saying:

Error 1418: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) SQL Statement:...

You should put DETERMINISTIC or other applicable declaration before BEGIN

DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

MySQL round half

4 Answers4

1. From Oracle documentation:

2. The code (tested in MySQL 5.6):

3. Rounding results in PHP:

4. Unit tests (in SQL):

Linked