SQL FLOOR

Summary: in this tutorial, you will learn how to use the SQL FLOOR() function to return the largest integer less than or equal to the specified number.

The FLOOR() function allows you to return the largest integer number less than or equal to the specified number.

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

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

In this syntax, the FLOOR() function accepts a literal number or a numeric expression that evaluates to a number. The argument can be the exact numeric type or floating-point type, which determines the type of the return number.

The following picture illustrates the FLOOR() function:

SQL FLOOR() function examples

See the following examples of using the FLOOR() function to understand how it works.

Using SQL FLOOR() function for a positive number

The following example uses the FLOOR() function for a positive number:

SELECT FLOOR(10.68);
Code language: SQL (Structured Query Language) (sql)

The return value is 10 because it is the largest integer less than or equal to 10.68

Using FLOOR() function for a negative number

The following example uses the FLOOR() function for a negative number:

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

In this example, because the largest integer less than or equal to -10.68 is -11, the FLOOR() function returned -11.

Using FLOOR() function in the query

We will use the employees and departments table from the sample database for the demonstration purposes:

The following statement finds the average salary of employees in each department. It uses the FLOOR() function to get the average salary as integer numbers:

SELECT 
    department_name, 
    FLOOR(AVG(salary)) average_salary
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
GROUP BY 
    e.department_id
ORDER BY 
    department_name;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

In this tutorial, you have learned about the SQL FLOOR() function and how to use it to find the largest integer number less than or equal to the specified number.