SQL FLOOR() function

FLOOR() function

The SQL FLOOR() function rounded up any positive or negative decimal value down to the next least integer value. SQL DISTINCT along with the SQL FLOOR() function is used to retrieve only unique value after rounded down to the next least integer value depending on the column specified.

Syntax:

 FLOOR(expression) 

Parameters:

MySQL, PostgreSQL, SQL Server, and Oracle

All of above platforms support the SQL syntax of FLOOR().

Visual presentation of FLOOR() Function

Example:

To get the rounded down to next integer value of 17.36 from the DUAL table, the following SQL statement can be used:

-- This SQL statement calculates the floor value of a positive number using the FLOOR function and selects the result from the 'dual' table.
SELECT FLOOR(17.36) 
-- SELECT statement retrieves data from the database
-- FLOOR() function is used to find the largest integer less than or equal to the specified number
-- In this case, FLOOR() is applied to 17.36
FROM dual;
-- Specifies the 'dual' table, a special one-row, one-column table present in Oracle database
-- The 'dual' table is often used for performing calculations or returning single results in SQL queries

Explanation:

• This SQL query is straightforward, as it's only a single statement.
• The purpose of this query is to find the floor value of a positive number, specifically 17.36.
• FLOOR() is a mathematical function that rounds a number down to the nearest integer, returning the largest integer that is less than or equal to the given number.
• In this case, FLOOR(17.36) will return 17 because 17 is the largest integer less than or equal to 17.36.
• The 'dual' table is used here because it's a convenient way to execute single-row queries in Oracle SQL without needing to specify an actual table with data.

Output:

FLOOR(17.36)
------------
          17

SQL FLOOR() function on negative value

To get the rounded down to next integer value of -17.36 from the DUAL table, the following SQL statement can be used :

-- This SQL statement calculates the floor value of a negative number using the FLOOR function and selects the result from the 'dual' table.
SELECT FLOOR(-17.36) 
-- SELECT statement retrieves data from the database
-- FLOOR() function is used to find the largest integer less than or equal to the specified number
-- In this case, FLOOR() is applied to -17.36
FROM dual;
-- Specifies the 'dual' table, a special one-row, one-column table present in Oracle database
-- The 'dual' table is often used for performing calculations or returning single results in SQL queries

Explanation:

• This SQL query is straightforward, as it's only a single statement.
• The purpose of this query is to find the floor value of a negative number, specifically -17.36.
• FLOOR() is a mathematical function that rounds a number down to the nearest integer, returning the largest integer that is less than or equal to the given number.
• In this case, FLOOR(-17.36) will return -18 because -18 is the largest integer less than or equal to -17.36.
• The 'dual' table is used here because it's a convenient way to execute single-row queries in Oracle SQL without needing to specify an actual table with data.

Output:

FLOOR(-17.36)
-------------
          -18

SQL FLOOR() function with distinct

To get the rounded down to next integer value of column 'commission' after multiplying by (-50) from the 'agents' table, the following SQL statement can be used :

-- This SQL statement selects distinct floor values of a specific expression involving commission amounts multiplied by -50, along with the original expression result, from the 'agents' table.
SELECT DISTINCT(FLOOR(commission*(-50))) "DISTINCT(FLOOR())", 
-- SELECT statement retrieves data from the database
-- DISTINCT keyword ensures that only unique values are returned
-- FLOOR() function is used to find the largest integer less than or equal to the specified number
-- The expression commission*(-50) is evaluated for each row, multiplying the commission by -50 before applying the FLOOR() function
-- "DISTINCT(FLOOR())" is an alias for the resulting column of distinct floor values
commission*(-50) 
-- This part of the SELECT statement calculates the expression commission multiplied by -50 for each row
-- This is included in the output to show the original expression result
FROM agents;
-- Specifies the 'agents' table from which the data is being retrieved

Explanation:

• This SQL query is designed to calculate the floor value of a specific expression involving commission amounts multiplied by -50 for each row in the 'agents' table.
• The DISTINCT keyword ensures that only unique floor values are returned, preventing duplicates.
• The FLOOR() function is used to find the largest integer less than or equal to the specified number.
• In this case, commission*(-50) calculates the commission amount multiplied by -50 before applying the FLOOR() function.
• The query also includes commission*(-50) in the output to show the original expression result for each row.
• The query is executed against the 'agents' table to retrieve the required data.

Output:

DISTINCT(FLOOR()) COMMISSION*(-50)
----------------- ----------------
               -6             -5.5
               -8             -7.5
               -6               -6
               -7               -7
               -7             -6.5

SQL: Comparing between FLOOR() and CEIL() function

Note: Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

Here is a slide presentation which covers the SQL arithmetic functions.

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: CEIL
Next: EXP