![SQL Floor() function Explained [Practical Examples]](/sql-floor-function/sql_floor.jpg)
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 thebitdata 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
Related Keywords: sql floor, floor sql, floor function in sql, floor in sql, floor sql server, sql floor function, sql server floor
