ROUND, CEIL, FLOOR in MySQL

Introduction

These functions help you change numbers to simpler forms. ROUND makes numbers closer to a chosen decimal place. CEIL rounds numbers up to the smallest whole number greater than or equal to it. FLOOR rounds numbers down to the largest whole number less than or equal to it.

When you want to show prices rounded to two decimal places.

When you need to count how many full boxes you need if you have some items left over.

When you want to ignore the decimal part and get the largest whole number less than or equal to a value.

When calculating age from birthdate and you want to round down to full years.

When you want to display a number without decimals for easier reading.

Syntax

MySQL

ROUND(number, decimals)
CEIL(number)
FLOOR(number)

ROUND can take two arguments: the number and how many decimals to keep. If decimals is missing, it rounds to the nearest whole number.

CEIL rounds up to the smallest whole number greater than or equal to the number.

FLOOR rounds down to the largest whole number less than or equal to the number.

Examples

Rounds 3.14159 to 2 decimal places, result is 3.14.

MySQL

SELECT ROUND(3.14159, 2);

Rounds 4.2 up to the next whole number, result is 5.

MySQL

SELECT CEIL(4.2);

Rounds 4.8 down to the previous whole number, result is 4.

MySQL

SELECT FLOOR(4.8);

Rounds 5.678 to the nearest whole number, result is 6.

MySQL

SELECT ROUND(5.678);

Sample Program

This query shows how ROUND, CEIL, and FLOOR work on the number 7.456.

MySQL

SELECT ROUND(7.456, 1) AS rounded_value,
       CEIL(7.456) AS ceiling_value,
       FLOOR(7.456) AS floor_value;

Important Notes

If you use ROUND without the second argument, it rounds to the nearest whole number.

CEIL and FLOOR always return whole numbers (integers).

These functions work well for money, measurements, or any time you want simpler numbers.

Summary

ROUND changes numbers to a chosen decimal place or whole number.

CEIL rounds numbers up to the smallest whole number greater than or equal to it.

FLOOR rounds numbers down to the largest whole number less than or equal to it.