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.