SQL Floor() function Explained [Practical Examples]

Overview of SQL Floor() Function

• SQL Floor() function is used to calculate the largest integer value that is smaller than or equal to the given numeric value.
• SQL Floor() function performs roundoff on a number down to the nearest integer
• SQL DISTINCT along with the SQL Floor() function is used to retrieve only unique values after rounded down to the next least integer value depending on the column specified

 

SQL Floor () function Syntax

FLOOR ( numeric_expression )

Here,

numeric_expression : It is a numerical expression or approximate numeric data type category, except for the bit data type

 

SQL Floor () function return type

SQL Floor() function returns value in numerical expression integer datatype

 

SQL Floor() function Examples

Example-1: SQL Floor() function with positive floating-point numbers

Write SQL query to calculate roundoff floating-point numbers 5.25 and 8.90

SELECT FLOOR(5.25) AS 'Floor(5.25)', FLOOR(8.90) AS 'Floor(8.90)'

In the above query, the floor() function is applied to two floating numbers 5.25 and 8.90, it will return a rounded down value for both numbers like 5 and 8

OUTPUT:

 

Example-2: SQL Floor() function with negative floating-point numbers

Write SQL query to calculate roundoff of given negative numeric floating-point number -6.78

SELECT FLOOR(- 6.78) AS 'Floor() -6.78'

In the above query, the SQL floor() function is used to calculate the smaller nearby integer of the given negative floating-point number -6.78

OUTPUT:

 

Example-3: SQL Floor() function with column value

Write SQL query to calculate rounded down the value of column percentage for all students

SELECT student_id, semester, exam_date, FLOOR(totalmarks) AS 'floor() marks', FLOOR(percentage) AS 'floor() percentage'
FROM student_result

In the above query, SQL Floor() function is applied with totalmarks and percentage to down  roundoff  column values
OUTPUT:

 

Example-4: SQL FLOOR() function with distinct

Write SQL query to calculate the rounded down to next integer value of column percentage

SELECT DISTINCT FLOOR(percentage) AS 'Distinct Floor()', percentage AS Percentage
FROM student_result

• In the above query, the SQL floor() function is applied on percentage column values with the DISTINCT keyword to get distinct values for each column
• SQL floor() function calculates a down-rounded value for each value of percentage column

OUTPUT:

 

Example-5: SQL Floor() function with numeric expression

Write SQL query to add 50.05 marks to each obtainmark and calculate roundoff integer number of each obtainmark value

SELECT student_id, FLOOR(totalmarks + 50.05) AS 'Floor() obtainmarks ' FROM student_result

• In the above query, SQL Floor() function is used to calculate down roundoff of given numerical expression
• The Floor() function will first evaluate the numerical expression and then perform roundoff

OUTPUT:

 

Difference between SQL Floor() and SQL Ceiling() function

SQL Floor() function is used to calculate the largest integer value that is smaller than or equal to a given numeric value whereas SQL Ceil() function is used to calculate the smallest integer value that is bigger than or equal to a number

 

Example 6: Write SQL query to calculate roundoff of given numeric value 7.67

SELECT FLOOR(7.67) AS 'Floor() 7.67', CEILING(7.67) AS 'Ceil() 7.67'

• In the above query, the SQL Floor () function is applied with numeric expression to calculate the rounded larger integer number
• SQL ceiling () is applied with numeric expression to calculate the rounded smallest integer number

OUTPUT:

 

Summary

In this article, we have discussed an overview of the SQL Floor() function, the syntax of the SQL Floor() function with a numeric argument, explain the SQL function Floor() function with practical examples, and also covered the difference between SQL Floor() and ceiling() function with a practical example

 

Further Reading

SQL Floor() Function

 

Related Keywords: sql floor, floor sql, floor function in sql, floor in sql, floor sql server, sql floor function, sql server floor