Summary: in this tutorial, you will learn how to use the SQL Server FLOOR()
function to round a number down to the nearest integer that is less than or equal to the input value.
SQL Server FLOOR function syntax
The FLOOR
function rounds a number down to the nearest integer that is less than or equal to the input value.
Here’s the syntax of the FLOOR()
function:
FLOOR (numeric_expression)
Code language: SQL (Structured Query Language) (sql)
In this syntax, the numeric_expression
is a number or an expression that you want to round down to the nearest integer.
The FLOOR()
function returns a number with the type depending on the type of the numeric_expression
.
The following table shows the type of the input value and the corresponding type of the return value of the FLOOR()
function:
Input type | Return type |
---|---|
float, real | float |
decimal(p, s) | decimal(38, s) |
int, smallint, tinyint | int |
bigint | bigint |
money, smallmoney | money |
bit | float |
SQL Server FLOOR() function examples
Let’s take some examples of using the FLOOR()
function.
1) Using SQL Server FLOOR() function with positive numbers
The following example uses the FLOOR()
function to round down positive numbers:
SELECT
FLOOR(11.51) x,
FLOOR(11.25) y;
Code language: SQL (Structured Query Language) (sql)
Output:
x | y
---+----
11 | 11
Code language: SQL (Structured Query Language) (sql)
2) Using the FLOOR() function with negative numbers
The following statement uses the FLOOR()
function to round down negative numbers:
SELECT
FLOOR(-11.51) x,
FLOOR(-11.25) y;
Code language: SQL (Structured Query Language) (sql)
Output:
x | y
----+----
-12 | -12
Code language: SQL (Structured Query Language) (sql)
3) Using the FLOOR function with money values
The following example uses the FLOOR()
function to round down a money value:
SELECT
FLOOR($100/3) amount;
Code language: SQL (Structured Query Language) (sql)
Output:
amount
-------
33.0000
Code language: SQL (Structured Query Language) (sql)
4) Using the FLOOR function with table data
First, create a table called teams to store the team name and total members:
CREATE TABLE teams (
id INT IDENTITY PRIMARY KEY,
name VARCHAR(255) NOT NULL,
total_member INT NOT NULL
);
Code language: SQL (Structured Query Language) (sql)
Second, insert rows into the teams
table:
INSERT INTO
teams (name, total_member)
VALUES
('A', 12),
('B', 11),
('C', 15);
Code language: SQL (Structured Query Language) (sql)
Third, use the FLOOR
function to distribute 100 items to each team in the teams
table, ensuring each member receives the same number of items:
SELECT
name,
total_member,
FLOOR(100 / total_member) item_count
FROM
teams;
Code language: SQL (Structured Query Language) (sql)
Output:
name | total_member | item_count
-----+--------------+-----------
A | 12 | 8
B | 11 | 9
C | 15 | 6
(3 rows)
Code language: SQL (Structured Query Language) (sql)
Summary
- Use the
FLOOR()
function to round a number down to the nearest integer that is less than or equal to the input value.