SQL Server FLOOR() Function

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:

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 | 11Code 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 | -12Code 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.0000Code 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.