FLOOR()

Syntax

How it works

FLOOR() takes a number and returns the largest whole number that is less than or equal to it. It always rounds down: FLOOR(2.1), FLOOR(2.5) and FLOOR(2.9) all return 2. It is the mirror image of CEIL(), which rounds up.

The important detail is what "down" means for negative numbers. FLOOR rounds toward negative infinity, not toward zero. So FLOOR(-1.2) is -2, because -2 is the largest integer that is still less than or equal to -1.2. This is different from truncation, which simply drops the fractional part and moves toward zero. See TRUNCATE() for that behaviour.

A very common use of FLOOR() is bucketing continuous values into fixed ranges. Dividing, flooring and multiplying back, as in FLOOR(age / 10) * 10, snaps every value down to the start of its band, which groups ages into decades, prices into brackets, and timestamps into intervals. It is available in every major dialect: MySQL, PostgreSQL, SQL Server and SQLite. For rounding to the nearest integer rather than always down, use CEIL() or the standard ROUND function.

Examples

Round a value down to whole units

-- 4 boxes hold 10 items each; how many full boxes?
SELECT FLOOR(47 / 10.0) AS full_boxes;

full_boxes
----------
         4

Bucket ages into decades

-- snap every age down to the start of its decade
SELECT name, age,
       FLOOR(age / 10) * 10 AS age_band
FROM customers
ORDER BY age;

name  | age | age_band
------+-----+---------
 Sara |  19 |      10
 Omar |  27 |      20
 Lina |  34 |      30
 Ali  |  41 |      40

FLOOR with a negative number

-- FLOOR rounds toward negative infinity, not toward zero
SELECT FLOOR(-1.2) AS a,
       FLOOR(-0.1) AS b,
       FLOOR( 2.9) AS c;

  a |  b | c
----+----+---
 -2 | -1 | 2

FLOOR versus casting to an integer

-- CAST truncates toward zero; FLOOR rounds down.
-- They disagree on negative values.
SELECT FLOOR(-3.7)          AS floor_val,
       CAST(-3.7 AS INT)    AS cast_val;

floor_val | cast_val
----------+---------
       -4 |       -3

Common mistakes

-- Expecting FLOOR to drop the fraction (truncate).
-- For -1.9 this returns -2, not -1.
SELECT FLOOR(-1.9);   -- -2

-- Use TRUNCATE (or CAST to int) to move toward zero
SELECT TRUNCATE(-1.9, 0);   -- -1

-- Using FLOOR when you meant "nearest"
-- FLOOR(2.8) is 2, losing the near round-up
SELECT FLOOR(2.8) AS score;   -- 2

-- ROUND goes to the nearest integer
SELECT ROUND(2.8) AS score;   -- 3

Performance

FLOOR() is a cheap scalar operation applied per row, so it has almost no cost on its own. The performance trap is wrapping an indexed column in FLOOR() inside a WHERE clause, as in WHERE FLOOR(price) = 10. That makes the predicate non-sargable: the database must compute FLOOR for every row and cannot use an index on price.

When you need to filter on a floored value, rewrite the condition as a range on the raw column, for example WHERE price >= 10 AND price < 11, which lets the optimiser use an index range scan. Reserve FLOOR for the SELECT list and for GROUP BY bucketing, where it runs once per output row rather than blocking an index.

Interview questions

SELECT FLOOR(-3.7)       AS floor_val,
       TRUNCATE(-3.7, 0) AS trunc_val;

SELECT FLOOR(age / 10) * 10 AS age_band,
       COUNT(*)             AS customers
FROM customers
GROUP BY FLOOR(age / 10) * 10
ORDER BY age_band;