FLOOR()
SQL · Math Functions
Theory
FLOOR rounds a number down to the nearest whole number. FLOOR(4.8) is 4, FLOOR(4.2) is 4 and FLOOR(5) stays 5. 'Down' means towards negative infinity, so FLOOR(-4.2) is -5. This differs from simply cutting off the decimals (CAST to integer, or TRUNC), which gives -4 for negatives.
FLOOR is ideal when only complete units count, such as full years of service, completed hours, or whole multiples of a pack size. It is also the classic way to put numbers into equal-width buckets: FLOOR(salary / 10000) * 10000 maps ₹58,000 to the 50,000 band and ₹65,000 to the 60,000 band, which you can then GROUP BY to build a histogram. FLOOR is spelled the same in PostgreSQL, MySQL, SQL Server, Oracle and Snowflake. SQLite provides floor() when math functions are enabled; for positive numbers, CAST(x AS INTEGER) gives the same result.
Why we use it
Many rules count only finished units: completed years, full batches, whole pages filled. FLOOR drops the unfinished part. It is also the simplest way to group continuous values into fixed-size ranges.
When to use it
Use FLOOR for bucketing (salary bands, age groups), completed-unit counts and integer division with decimals. Use CEIL when partial units must be counted as whole, and ROUND for the nearest value.
How it works
- Input number, e.g. 58000 / 10000 = 5.8
- Is it already a whole number? Yes: Return it unchanged. No: Continue.
- Move down to the next integer towards -infinity
- Optionally multiply back: 5 * 10000 = 50000 band
- Group or label rows by that bucket
Syntax
FLOOR(number)Example
SELECT FLOOR(4.8);
-- 4Key points
- Always rounds toward negative infinity
- Opposite of CEIL()
- FLOOR(-4.2) is -5, while CAST/TRUNC cut it to -4
- FLOOR(x / width) * width is the standard bucketing pattern for histograms
- In SQLite without math functions, CAST(x AS INTEGER) matches FLOOR for positives