SQL FLOOR: Rounding Down to the Previous Integer and Integer Bucketing

FLOOR rounds a number down to the nearest integer that is not greater than the input. It is the mathematical "floor": on the number line you always move left, toward the smaller integer.

What FLOOR does

The function takes any number and returns the largest integer that is less than or equal to the argument. The fractional part is simply dropped, but only for non-negative numbers.

SELECT
  FLOOR(4.9)  AS a,   -- 4
  FLOOR(4.1)  AS b,   -- 4
  FLOOR(4.0)  AS c,   -- 4
  FLOOR(-4.1) AS d;   -- -5

Key properties:

• The rounding is always down, never to the nearest: 4.9 becomes 4, not 5.
• An integer argument is unchanged: FLOOR(4.0) is 4.
• In PostgreSQL the result type matches the argument: numeric stays numeric, double precision stays double precision. Add ::int when you want a real integer.

Negative numbers move further from zero

This is the main trap. For positive numbers FLOOR looks like it just "chops off" the fraction, but for negatives it rounds further from zero, because the smaller integer lies below.

SELECT
  FLOOR(2.7)  AS pos,   -- 2
  FLOOR(-2.7) AS neg;   -- -3

Compare this with TRUNC, which always discards the fraction and moves toward zero:

SELECT
  TRUNC(-2.7) AS truncated,   -- -2
  FLOOR(-2.7) AS floored;     -- -3

Gotcha: if you compute age brackets, scores, or coordinates where negative values appear, FLOOR and TRUNC give different results. Use TRUNC for "drop the fraction"; use FLOOR for "round down on the axis".

Bucketing: FLOOR(x/n)*n

The most practical trick is to bin values into equal intervals. To group order amounts into buckets of 100, divide by 100, apply FLOOR, and multiply back.

SELECT
  FLOOR(amount / 100) * 100 AS bucket,
  COUNT(*)                  AS orders
FROM orders
WHERE status = 'paid'
GROUP BY FLOOR(amount / 100) * 100
ORDER BY bucket;

An order of 250 lands in bucket 200, one of 99 in bucket 0. The same trick spreads employee salaries into bands of ten thousand:

SELECT
  FLOOR(salary / 10000) * 10000 AS salary_band,
  COUNT(*)                      AS headcount
FROM employees
GROUP BY FLOOR(salary / 10000) * 10000
ORDER BY salary_band;

Gotcha: watch out for integer division. If amount is an integer, then amount / 100 is already truncated to an integer before FLOOR ever runs, and the buckets drift. Cast to a fractional type: FLOOR(amount::numeric / 100) * 100.

Paired with CEIL and dialect differences

FLOOR always travels with CEIL (also CEILING), which rounds up. Together they clamp a value into an integer range.

SELECT
  FLOOR(4.2) AS low,   -- 4
  CEIL(4.2)  AS high;  -- 5

Dialect notes:

• PostgreSQL: FLOOR keeps the numeric type of the argument, and negatives are handled by strict math.
• MySQL: FLOOR over DECIMAL returns an integer, but for DOUBLE the result is also DOUBLE; be careful when comparing types.
• ClickHouse: offers floor(x) plus the extension floor(x, N) that rounds to N decimal places, which standard SQL does not have.

A common useful pattern is spreading rows evenly across N groups for sampling or sharding:

SELECT
  id,
  FLOOR(RANDOM() * 4)::int AS shard   -- 0..3
FROM users;

Remember three things: FLOOR rounds down, negatives move further from zero, and for "chop the fraction toward zero" you want TRUNC, not FLOOR.