In this tutorial, we will learn about the MySQL FLOOR()
and CEIL()
functions. The FLOOR()
function accepts a number or a numeric expression as an argument and returns the largest integer value that is less than or equal to the argument. For instance, if you pass 23.7 in FLOOR()
, it will return you the largest integer value that is less than or equal to 23.7. In this case, it will return 23 as the result.
The CEIL()
function accepts a number or a numeric expression as an argument and returns the smallest integer value that is greater than or equal to the argument. The CEIL()
function can also be written as CEILING()
. For instance, if you pass 23.7 in CEIL()
, it will return you the smallest integer value that is greater than or equal to 23.7. In this case, it will return 24 as the result.
Syntax of MySQL FLOOR()
FLOOR(expression);
Code language: SQL (Structured Query Language) (sql)
Where, ‘expression’ can be a number or a numeric expression.
Syntax of MySQL CEIL()
CEIL()
can also be written as CEILING()
and hence, we have two syntaxes for this function. You may use either one of them.
CEIL(expression);
Code language: SQL (Structured Query Language) (sql)
Or,
CEILING(expression);
Code language: SQL (Structured Query Language) (sql)
Where, ‘expression’ can be a number or a numeric expression.
Examples of MySQL FLOOR()
Let us take a look at a few basic examples of MySQL FLOOR()
. Let us find what value will be returned when we pass the following numbers in FLOOR()
– 32.25, 12.8 and 0.5. The query for it is as follows. We make use of the SELECT
statement.
SELECT FLOOR(32.25);
SELECT FLOOR(12.8);
SELECT FLOOR(0.5);
Code language: SQL (Structured Query Language) (sql)
Before you see the output below, guess as to what value each of the above queries would return.
![MySQL Floor Basic Examples](https://mysqlcode.com/wp-content/uploads/2021/01/floor-basic-examples.png)
MySQL FLOOR() with Negative Decimals Numbers
Now, take a moment and guess what would be the output if you pass a negative number, say -4.3 to MySQL FLOOR()
. The largest integer less than -4.3 is -5 and hence, FLOOR()
will output that as our result. Let us look at the below examples.
SELECT FLOOR(-4.3); SELECT FLOOR(-4.8);
Code language: SQL (Structured Query Language) (sql)
And the output is,
![Floor Negative Numbers Example](https://mysqlcode.com/wp-content/uploads/2021/01/floor-negative-numbers-example.png)
Examples of MySQL CEIL()
Let us take a look at a few basic examples of MySQL CEIL()
. Let us find what value will be returned when we pass the following numbers in CEIL()
– 32.25, 12.8 and 0.5. The query for it is as follows. We make use of the SELECT
statement.
SELECT CEIL(32.25); SELECT CEIL(12.8); SELECT CEIL(0.5);
Code language: SQL (Structured Query Language) (sql)
Before you see the output below, guess as to what value each of the above queries would return.
![Ceil Basic Examples](https://mysqlcode.com/wp-content/uploads/2021/01/ceil-basic-examples.png)
MySQL CEIL() with Negative Numbers
We saw earlier how MySQL FLOOR()
reacts to negative numbers as arguments. Let us now see how CEIL()
does. Now, take a moment and guess what would you get the output if you pass a negative number, say -4.3 to CEIL()
. The smallest integer greater than -4.3 is -4 and hence, CEIL()
will output that as our result. Let us look at the below examples.
SELECT CEIL(-4.3); SELECT CEIL(-4.8);
Code language: SQL (Structured Query Language) (sql)
And the output is,
![Ceil Negative Numbers Example](https://mysqlcode.com/wp-content/uploads/2021/01/ceil-negative-numbers-example.png)
FLOOR() and CEIL() – Working with Tables
Consider the below Sphere table.
![Sphere Table MySQL Ceil Floor](https://mysqlcode.com/wp-content/uploads/2021/01/sphere-table-ceil-floor.png)
Basic Examples
Let us get the FLOOR()
value for all the values in the SurfaceArea
column. Our query is,
SELECT SurfaceArea, FLOOR(SurfaceArea) FROM Sphere;
Code language: SQL (Structured Query Language) (sql)
And the output is,
![Sphere Table Floor Example](https://mysqlcode.com/wp-content/uploads/2021/01/sphere-table-floor-example.png)
Now, let us see what result we get if we do the same operation, but this time using the CEIL()
function. The query is,
SELECT SurfaceArea, CEIL(SurfaceArea) FROM Sphere;
Code language: SQL (Structured Query Language) (sql)
And the output is,
![Sphere Table Ceil Example](https://mysqlcode.com/wp-content/uploads/2021/01/sphere-table-ceil-example.png)
Having An Expression As Parameter in FLOOR() and CEIL()
So far, we have seen only numbers as arguments in FLOOR()
and CEIL()
. How about having expressions as arguments? Let us find the floor value of the average of all radius values in the Sphere table. The query is,
SELECT FLOOR(AVG(Radius)) AS Approx_Average_Radius FROM Sphere;
Code language: SQL (Structured Query Language) (sql)
And the output is,
![Floor Avg Radius Example](https://mysqlcode.com/wp-content/uploads/2021/01/floor-avg-radius-example.png)
Similarly, let us find the ceil value of the sum of all the surface area values of the sphere. The query is,
SELECT CEIL(SUM(SurfaceArea)) AS Approx_Sum_Of_SurfaceArea FROM Sphere;
Code language: SQL (Structured Query Language) (sql)
And the output is,
![Ceil Sum Surfacearea](https://mysqlcode.com/wp-content/uploads/2021/01/ceil-sum-surfacearea.png)
Conclusion
FLOOR()
and CEIL()
may get very confusing and I encourage you to keep practicing them. Both functions are very useful in a variety of mathematical operations. I would recommend you to check out the below references.
References
- MySQL Official Documentation on
FLOOR()
. - MySQL Official Documentation on
CEIL()
.