# SQL Floor() function Explained [Practical Examples]

Written By - Falguni Thakker

## 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

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

Categories SQL

Didn't find what you were looking for? Perform a quick search across GoLinuxCloud

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation. For any other feedbacks or questions you can either use the comments section or contact me form.