SQL FLOOR Function

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

Output:

 result
--------
     10

Using FLOOR() function for a negative number #

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

SELECT FLOOR(-10.68) result;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:

 result
--------
    -11

Using FLOOR() function with table data #

We’ll 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
  department_name
ORDER BY
  department_name;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 department_name  | average_salary
------------------+----------------
 Accounting       |          10150
 Administration   |           4400
 Executive        |          19333
 Finance          |           8600
 Human Resources  |           6500
 IT               |           5760
 Marketing        |           9500
 Public Relations |          10000
 Purchasing       |           4150
 Sales            |           9616
 Shipping         |           5885Code language: PHP (php)

Summary #

• Use the FLOOR function to find the largest integer number less than or equal to the specified number.