MySQL FLOOR Function

 MySQL FLOOR Function

Summary: in this tutorial, you will learn how to use the MySQL FLOOR() function.

The FLOOR() the function accepts one argument which can be a 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)

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 the 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);
-- 1

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);
-- -2

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;

Here is the output:

Because the AVG() the 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.