# SQL Floor() function Explained [Practical Examples]

Topics we will cover

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

Views: 11

Falguni Thakker

She is a dedicated professional with expertise in SQL, Python, C++, and Linux. Currently serving as a professor at a prestigious university. With a passion for teaching and a strong technical background, she inspires the next generation of computer scientists. You can connect with her on LinkedIn.

Can't find what you're searching for? Let us assist you.

Enter your query below, and we'll provide instant results tailored to your needs.

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 send mail to admin@golinuxcloud.com