SQL Reference ยท Scalar Functions
SQL CEIL() and FLOOR() functions
Reference for SQL CEIL() / CEILING() and FLOOR(): round up or down to a whole number, behavior with negatives, ceiling division and dialect differences.
CEIL(x) returns the smallest whole number that is greater than or equal to x. FLOOR(x) returns the largest whole number that is less than or equal to x. Together they round in a fixed direction, unlike ROUND(), which goes to the nearest whole number.
Typical uses are page counts (how many pages for 25 rows at 10 per page?), box counts, and bucketing numbers into ranges.
SELECT CEIL(25 / 10.0) AS pages; -- 3
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
CEIL(x) CEILING(x) FLOOR(x)
| Name | Description |
|---|---|
x |
A number. NULL gives NULL. |
Returns: a whole number. The data type depends on the database: numeric input stays numeric in PostgreSQL, while SQLite returns an integer for these inputs.
Dialect differences
| Database | Round up | Round down |
|---|---|---|
| Standard SQL | CEIL, CEILING |
FLOOR |
| SQLite | ceil() (and ceiling() in stock builds 3.35+) |
floor() |
| PostgreSQL | CEIL, CEILING |
FLOOR |
| MySQL | CEIL, CEILING |
FLOOR |
| SQL Server | CEILING only |
FLOOR |
| Oracle | CEIL only |
FLOOR |
The site's SQLite build has ceil() and floor() but not ceiling(). SQLite only has them when it was compiled with the math functions option; the official sqlite3 shell does include them, and some older builds do not.
-- SQL Server SELECT CEILING(2.1); -- Truncate toward zero: PostgreSQL TRUNC, MySQL TRUNCATE, Oracle TRUNC SELECT TRUNC(-2.7); -- -2
Usage notes
- Negatives go the other way than you may expect.
FLOOR(-2.1)is-3, not-2, andCEIL(-2.1)is-2. To drop the decimals toward zero, useCAST(x AS INTEGER). - Integer division comes first.
CEIL(25 / 10)is2in SQLite and SQL Server because25 / 10is already2. Make one side a decimal (10.0) to get3. - Ceiling division without the function:
(a + b - 1) / bgivesCEIL(a / b)for positive integers and works everywhere, including builds withoutceil(). - Bucketing:
FLOOR(x / size) * sizeis the start of the bucket that containsx; group by it for histograms. - Not the same as rounding.
FLOOR(2.9)is 2,ROUND(2.9)is 3. - Floating-point noise can push a value like
2.9999999999below a whole number. Round first if exact results matter.
Related
ROUND(),ABS(),MOD(),CAST- Lessons: Numeric functions, LIMIT and OFFSET