MySQL FLOOR Function
Summary: in this tutorial, you will learn how to use the MySQL FLOOR() function.
The FLOOR() function accepts one argument which can be number or numeric expression and returns the largest integer number less than or equal to the argument.
The following shows the syntax of the FLOOR() function:
FLOOR(expression)
Code language: SQL (Structured Query Language) (sql)
The data type of the return value depends on the type of the input number. If the type of the input number is exact numeric or floating-point type, the type of the returned value is exact numeric or floating-point type respectively.
The following picture illustrates the FLOOR() function:
MySQL FLOOR() function examples
Let’s take some examples of using the FLOOR() function to understand it better.
Applying FLOOR() function to a positive number
The following statement applies the FLOOR() function to a positive number:
SELECT FLOOR(1.59);
-- 1Code language: SQL (Structured Query Language) (sql)
The result is 1 because it is the largest integer which is less than or equal to 1.59
Applying FLOOR() function to a negative number
The following example applies the FLOOR() function to a negative number.
SELECT FLOOR(-1.59);
-- -2Code language: SQL (Structured Query Language) (sql)
The largest integer which is less than or equal to -1.59 is 2, therefore, the FLOOR() function returned -2.
Using FLOOR() function in the query
See the following products table from the sample database:
The following statement finds the average stock for each product line:
SELECT
productLine,
FLOOR(AVG(quantityInStock)) averageStock
FROM
products
GROUP BY
productLine
ORDER BY
averageStock;
Code language: SQL (Structured Query Language) (sql)
Here is the output:
Because the AVG() function returns a decimal value, we need to apply the FLOOR() function to the average result.
In this tutorial, you have learned how to use the MySQL FLOOR() function to find the largest integer number less than or equal to the input number.