MySQL FLOOR Function

Summary: in this tutorial, you will learn how to use the MySQL FLOOR() function to round a numeric value down to the nearest integer that is less than or equal to the original value

Introduction to MySQL FLOOR function

The FLOOR() function accepts an argument which can be a number or a numeric expression and returns the largest integer that is not greater than the original value.

Here’s the basic syntax of the FLOOR() function:

FLOOR(numeric_expression)Code language: SQL (Structured Query Language) (sql)

In this syntax, the numeric_expression is a numeric value or expression that you want to round down to the nearest integer.

If the numeric_expression is NULL, the FLOOR() function returns NULL.

MySQL FLOOR() function examples

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

1) Using the FLOOR() function with a positive number

The following shows how to use the FLOOR() function with a positive number:

SELECT FLOOR(1.59);
-- 1Code language: SQL (Structured Query Language) (sql)

In this example, the FLOOR() function returns 1 because it is the nearest integer that is less than or equal to 1.59.

2) Using the FLOOR() function with a negative number

The following example shows how to use the FLOOR() function with a negative number.

SELECT FLOOR(-1.59);
-- -2Code language: SQL (Structured Query Language) (sql)

In this example, the FLOOR() function returns -2 because it is the nearest integer that is less than or equal to -1.59.

3) Using the FLOOR() function in the query

We’ll use the products table from the sample database for the demonstration:

The following query uses the FLOOR() function to find 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)

Output:

Because the AVG() function returns a decimal value, we need to apply the FLOOR() function to get average stock in integer values.

Summary

• Use the MySQL FLOOR() function to find the largest integer number less than or equal to the input number.