MySQL FLOOR() function

In this context, we will learn how we can use the MySQL FLOOR() function with proper syntaxes and examples.

Introduction of MySQL Floor() function:

FLOOR() function in MySQL is used to return the largest integer value which will be either equal to or less than from a given input number.

Syntax of the MySQL Floor() function

The syntax of the MySQL FLOOR() function is given as follows:

FLOOR(expr);

The type of the input number depends on data type of the return value. The type of the returned value is exact numeric or floating-point type respectively, when the type of the input number is exact numeric or floating-point type.

Parameter or arguments used in MySQL Floor() function:

Only one parameter is accepted by the FLOOR (expr) Function, as mentioned above and described below:

expr: This is the expression whose floor value we want to calculate.

Returns: It will return the closest integer which is <=X. So, if Y is integer than it will return Y. Otherwise, largest integer which is lesser than Y.

MySQL versions used for Floor() function:

The FLOOR() function can be used in the given below MySQL versions.

• MySQL 5.7
• MySQL 5.6
• MySQL 5.5
• MySQL 5.1
• MySQL 5.0
• MySQL 4.1
• MySQL 4.0
• MySQL 3.23

Examples of MySQL Floor() function:

Let’s take some examples of using the FLOOR() function to understand it better.

Example: 1

Now we will Apply FLOOR() function to a positive number in MySQL.

SELECT FLOOR(1.59);

The Output is:

1
1 row in set (0.00 sec)

The result is 1 because it is the largest integer which is less than or equal to 1.59.

Example: 2

Now we will Apply FLOOR() function to a negative number in MySQL.

SELECT FLOOR(1.59);

The Output is:

-2
1 row in set (0.00 sec)

The largest integer which is less than or equal to -1.59 is 2, therefore, the FLOOR() function returned -2.

Example: 3

Now we will Use FLOOR() function in the query of MySQL.

Lets See the following commoditys table from the sample database:

Commodity

Commoditycode Commodityname Commodityline Commodityscale Commodityvendor Commoditydescription Quantityinstock Buyprice Msrp

The following statement finds the average stock for each commodity line:

SELECT
commodityLine,
FLOOR(AVG(quantityInStock)) averageStock
FROM
commoditys
GROUP BY
commodityLine
ORDER BY
averageStock;

Here is the output:

Commodityline	Averagemsrp
Trains	2154
Ships	2365
Vintage cars	2973
Planes	3482
Motorcycles	3894
Trucks and buses	4107
Classic cars	5115

Because the AVG() function returns a decimal value, we need to apply the FLOOR() function to the average result.

Some more examples:

Example 4:

Now we will Apply FLOOR() function to a +ve integer in MySQL:

SELECT FLOOR(4) AS Floor_Value;

Output:

Floor_Value

4
1 row in set (0.00 sec)

Example 5 :

Now we will Apply FLOOR() function to a -ve integer.

SELECT FLOOR(-6) AS Floor_Value;

Output:

Floor_Value
-6
1 row in set (0.00 sec)

Example 6:

Now we will Apply FLOOR() function to a +ve floating number.

SELECT FLOOR(1.5) AS Floor_Value;

Output:

Floor_Value
1
1 row in set (0.00 sec)

Example 7:

Now we will Apply FLOOR() function to a -ve floating number.

SELECT FLOOR(-1.5) AS Floor_Value;

Output:

Floor_Value
-2
1 row in set (0.00 sec)

Example 8:

Now we will use FLOOR value of a numeric column in a table.

Table – Number

X

90.55

0

-9

-45.76

0.25

SELECT X, FLOOR(X) AS X_Floor FROM Number;

The Output is:

X	X_Floor
90.55	90
0	0
-9	-9
-45.76	-46
0.25	0

Summary:

In this context, we have learned how we can use the MySQL FLOOR() function to find the largest integer less than or equal to the number given.