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() 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,**

## 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.

### 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,**

## FLOOR() and CEIL() – Working with Tables

**Consider the below 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,**

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,**

### 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,**

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,**

## 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()`

.