MySQL FLOOR() and CEIL() Functions

By /
FLOOR And CEIL Functions

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

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

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

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

FLOOR() and CEIL() – Working with Tables

Consider the below Sphere table.

Sphere Table MySQL Ceil Floor
Sphere Table

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

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

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

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

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