MySQL Arithmetic Rounding (ROUND, FLOOR, CEIL)

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Insert
> Step 2: And Like the video. BONUS: You can also share it!

Example

Round a decimal number to an integer value

For exact numeric values (e.g. DECIMAL): If the first decimal place of a number is 5 or higher, this function will round a number to the next integer away from zero. If that decimal place is 4 or lower, this function will round to the next integer value closest to zero.

SELECT ROUND(4.51) -> 5
SELECT ROUND(4.49) -> 4
SELECT ROUND(-4.51) -> -5

For approximate numeric values (e.g. DOUBLE): The result of the ROUND() function depends on the C library; on many systems, this means that ROUND() uses the round to the nearest even rule:

SELECT ROUND(45e-1) -> 4  -- The nearest even value is 4
SELECT ROUND(55e-1) -> 6  -- The nearest even value is 6

Round up a number

To round up a number use either the CEIL() or CEILING() function

SELECT CEIL(1.23)    -> 2
SELECT CEILING(4.83) -> 5

Round down a number

To round down a number, use the FLOOR() function

SELECT FLOOR(1.99) -> 1

FLOOR and CEIL go toward / away from -infinity:

SELECT FLOOR(-1.01), CEIL(-1.01) -> -2 and -1
SELECT FLOOR(-1.99), CEIL(-1.99) -> -2 and -1

Round a decimal number to a specified number of decimal places.

SELECT ROUND(1234.987, 2) -> 1234.99
SELECT ROUND(1234.987, -2) -> 1200

The discussion of up versus down and "5" applies, too.



Got any MySQL Question?