NumbersSQL standard

FLOOR

Rounds a number down — to the nearest integer that isn't larger than it.

Syntax

FLOOR(<number>)

Drops the fractional part towards the smaller number: 4.9 becomes 4. An integer stays as it is. Handy for sorting values into ranges: amounts by hundreds, ages by decades.

Data for the examples

orders

id	created_at	amount
1	2026-07-03 10:15:00	120.50
2	2026-07-28 18:40:00	80.25
3	2026-08-09 09:05:00	199.99
4	2026-08-21 14:30:00	45.00
5	2026-09-02 11:00:00	64.75

These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.

Down to an integer

SELECT FLOOR(4.9) AS down,

FLOOR(4.0) AS whole,

FLOOR(-4.1) AS negative;

down	whole	negative
4	4	-5

Each order in its own amount range

SELECT id, amount,

FLOOR(amount / 100) * 100 AS price_range

FROM orders;

id	amount	price_range
1	120.50	100
2	80.25	0
3	199.99	100
4	45.00	0
5	64.75	0

How many orders in each range

SELECT FLOOR(amount / 100) * 100 AS price_range,

COUNT(*) AS order_count

FROM orders

GROUP BY price_range

ORDER BY price_range;

price_range	order_count
0	3
100	2

0 means amounts from 0 to 99.99, 100 — from 100 to 199.99.

Watch out

For negative numbers, “down” means further from zero: FLOOR(-4.1) gives -5. Simply dropping the fractional part is TRUNC: TRUNC(-4.1) gives -4.

Integer division drops the fractional part towards zero, not down: -7 / 2 gives -3, while FLOOR(-7 / 2.0) gives -4.

Related functions

CEIL ROUND TRUNC

Learn more in the course

Numbers and rounding