How to round down to nearest integer in MySQL?
Solution 1
Use FLOOR:
SELECT FLOOR(your_field) FROM your_table
Solution 2
Use FLOOR().
It will to round your decimal to the lower integer. Examples:
SELECT FLOOR(1.9) /* return 1 */
SELECT FLOOR(1.1) /* return 1 */
Other useful rounding
If you want to round your decimal to the nearest integer, use ROUND(). Examples:
SELECT ROUND(1.9) /* return 2 */
SELECT ROUND(1.1) /* return 1 */
If you want to round your decimal to the upper integer, use CEILING(). Examples:
SELECT CEILING(1.9) /* return 2 */
SELECT CEILING(1.1) /* return 2 */
Solution 3
SELECT FLOOR(12345.7344);
Read more here.
Solution 4
SUBSTR
will be better than FLOOR
in some cases because FLOOR has a "bug" as follow:
SELECT 25 * 9.54 + 0.5 -> 239.00
SELECT FLOOR(25 * 9.54 + 0.5) -> 238 (oops!)
SELECT SUBSTR((25*9.54+0.5),1,LOCATE('.',(25*9.54+0.5)) - 1) -> 239
Solution 5
It can be done in the following two ways:
select floor(desired_field_value) from table
select round(desired_field_value-0.5) from table
The 2nd-way explanation: Assume 12345.7344 integer. So, 12345.7344 - 0.5 = 12345.2344 and rounding off the result will be 12345.
Comments
-
d-_-b almost 2 years
How would I round down to the nearest integer in MySQL?
Example:
12345.7344 rounds to 12345
mysql's
round()
function rounds up.I don't know how long the values nor the decimal places will be, could be 10 digits with 4 decimal places, could be 2 digits with 7 decimal places.
-
d-_-b over 11 yearsThank you. It was staring me in the face the whole time I was reading about round() .
-
tread almost 8 yearsOk I've deleted that comment, I did try this and it returned 0 when the
FLOOR()
was the difference between 2 subqueries (subquery difference was -70.00)...ever seen that before? -
Winter Dragoness almost 6 yearsI just tested SELECT FLOOR(25 * 9.54 + 0.5) on MySQL 5.6.40 and it returns 239.
-
forpas over 3 years
FLOOR()
has already been posted as an answer. Where did you find the functionNULL()
? -
Swapnadeep Mukherjee over 2 yearsRound() function worked for me.