{"id":1686,"date":"2018-09-06T05:16:36","date_gmt":"2018-09-06T12:16:36","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=1686"},"modified":"2025-03-16T20:23:27","modified_gmt":"2025-03-17T03:23:27","slug":"sql-floor","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-math-functions\/sql-floor\/","title":{"rendered":"SQL FLOOR Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL FLOOR()<\/code> function to return the largest integer less than or equal to the specified number.<\/p>\n\n\n\n

The FLOOR()<\/code> function allows you to return the largest integer number less than or equal to the specified number.<\/p>\n\n\n\n

The syntax of the FLOOR()<\/code> function is as follows:<\/p>\n\n\n

FLOOR(numeric_expression)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the FLOOR()<\/code> function accepts a literal number or a numeric expression that evaluates to a number. <\/p>\n\n\n\n
The argument can be the exact numeric type or floating-point type, which determines the type of the return number.<\/p>\n\n\n\n
The following picture illustrates the FLOOR()<\/code> function:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
SQL FLOOR() function examples #<\/a><\/h2>\n\n\n\n
See the following examples of using the FLOOR()<\/code> function to understand how it works.<\/p>\n\n\n\n
Using SQL FLOOR() function for a positive number #<\/a><\/h3>\n\n\n\n
The following example uses the FLOOR()<\/code> function for a positive number:<\/p>\n\n\n
SELECT<\/span> FLOOR<\/span>(10.68<\/span>) result<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The return value is 10 because it is the largest integer less than or equal to 10.68<\/p>\n\n\n\n
Output:<\/p>\n\n\n
 result\n--------\n     10<\/code><\/span><\/pre>\n\n\n
Using FLOOR() function for a negative number #<\/a><\/h3>\n\n\n\n
The following example uses the FLOOR()<\/code> function for a negative number:<\/p>\n\n\n
SELECT<\/span> FLOOR<\/span>(-10.68<\/span>) result<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, because the largest integer less than or equal to -10.68 is -11, the FLOOR()<\/code> function returned -11:<\/p>\n\n\n
 result\n--------\n    -11<\/code><\/span><\/pre>\n\n\n
Using FLOOR() function with table data #<\/a><\/h3>\n\n\n\n
We’ll use the employees<\/code>\u00a0and departments<\/code>\u00a0table from the sample database<\/a> for the demonstration purposes:<\/p>\n\n\n\n
\"Employees<\/figure>\n\n\n\n
The following statement finds the average salary of employees in each department. It uses the FLOOR()<\/code> function to get the average salary as integer numbers:<\/p>\n\n\n
SELECT<\/span>\n  department_name,\n  FLOOR<\/span>(AVG<\/span>(salary)) average_salary\nFROM<\/span>\n  employees e\n  INNER<\/span> JOIN<\/span> departments d ON<\/span> d.department_id = e.department_id\nGROUP<\/span> BY<\/span>\n  department_name\nORDER<\/span> BY<\/span>\n  department_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 department_name  | average_salary\n------------------+----------------\n Accounting       |          10150<\/span>\n Administration   |           4400<\/span>\n Executive        |          19333<\/span>\n Finance          |           8600<\/span>\n Human Resources  |           6500<\/span>\n IT               |           5760<\/span>\n Marketing        |           9500<\/span>\n Public<\/span> Relations |          10000<\/span>\n Purchasing       |           4150<\/span>\n Sales            |           9616<\/span>\n Shipping         |           5885<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the FLOOR<\/code> function to find the largest integer number less than or equal to the specified number.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Try it<\/a><\/p>\n\n\n\n