How to use floor() function in MySQL8 ?

by · Published · Updated

With MySQL, we can do complex calculations very easily with the help of inbuilt mathematical functions. FLOOR(X) returns the largest integer value not greater than X.

Now let’s start with it.

FLOOR(X) is a mathematical function. It returns the largest integer value not greater than X. For integer values, the return value is an integer value. For string or floating-point arguments, the return value has a floating-point type.

It will return NULL, if X is passed as NULL.

It returns Error, if any String argument is passed as an input.

MySQL FLOOR() : Syntax

FLOOR ( X );

MySQL FLOOR() : Parameter

Name, Required /Optional, Description
X , Required, Double , It represents a valid number.

MySQL FLOOR() : Output

Return, Description
NULL, if the argument is NULL.
Double, It returns the largest integer value not greater than X.

MySQL FLOOR()  Available from : MySQL 4.1

FLOOR() Example 1 : Basic Examples

Now, Let’s see some of the basic examples of it and see what it returns. You can see in the below example, we have got the output 1 when user input is 1.45. the returned value is the largest of X not greater than it.

mysql> SELECT FLOOR(1.45);
+-------------+
| FLOOR(1.45) |
+-------------+
| 1 |
+-------------+
1 row in set (0.07 sec)

mysql> SELECT FLOOR(56);
+-----------+
| FLOOR(56) |
+-----------+
| 56 |
+-----------+
1 row in set (0.00 sec)

FLOOR() Example 2 : Using it with NEGATIVE numbers

We are using the FLOOR() function with negative values. I have got the below output value.

mysql> SELECT FLOOR(-1.45);
+--------------+
| FLOOR(-1.45) |
+--------------+
| -2 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT FLOOR(-56);
+------------+
| FLOOR(-56) |
+------------+
| -56 |
+------------+
1 row in set (0.00 sec)

FLOOR() Example 3 : Using it with Expressions

Let’s see the below examples.

mysql> SELECT FLOOR(1.45+5.89);
+------------------+
| FLOOR(1.45+5.89) |
+------------------+
| 7 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT FLOOR(1.45-5.89);
+------------------+
| FLOOR(1.45-5.89) |
+------------------+
| -5 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT FLOOR(6.45*3.18);
+------------------+
| FLOOR(6.45*3.18) |
+------------------+
| 20 |
+------------------+
1 row in set (0.00 sec)

FLOOR() Example 4 : NULL arguments

If the argument is NULL, it will return NULL. See the below example :

mysql> SELECT FLOOR(NULL);
+-------------+
| FLOOR(NULL) |
+-------------+
| NULL |
+-------------+
1 row in set (0.04 sec)

Related articles : ABS(), MySQL Maths.

Tags:

You may also like...