MySQL Arithmetic Rounding (ROUND, FLOOR, CEIL)
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.
