CEIL()
SQL · Math Functions
Theory
CEIL, short for ceiling, rounds a number up to the next whole number. CEIL(4.2) is 5, CEIL(4.8) is 5 and CEIL(5) stays 5. 'Up' means towards positive infinity, so for negatives CEIL(-4.8) is -4, not -5, which surprises many beginners.
CEIL is the right tool whenever a partial unit still counts as a whole one. If one box holds 6 items, an order of 14 items needs CEIL(14 / 6.0) = 3 boxes; if a page shows 10 rows, 95 results need CEIL(95 / 10.0) = 10 pages. Spelling varies slightly: PostgreSQL, MySQL, Oracle and Snowflake accept CEIL (and usually CEILING), while SQL Server only has CEILING. SQLite has ceil() and ceiling() when it is built with math functions; otherwise you can emulate it with a CASE on CAST(x AS INTEGER). As with ROUND, divide by a decimal (6.0) so integer division does not throw away the remainder first.
Why we use it
Real-world quantities often cannot be partial: you cannot ship 2.3 boxes or book 1.5 rooms. CEIL turns a fractional need into the whole number of units required, so you never under-count.
When to use it
Use CEIL for packing, paging, billing by started hour and capacity planning. Use FLOOR when only completed units count (full years of service), and ROUND when you want the nearest value.
How it works
- Input number, e.g. 14 / 6.0 = 2.33
- Is it already a whole number? Yes: Return it unchanged. No: Continue.
- Move up to the next integer towards +infinity
- Return 3 (and -4 for -4.8)
Syntax
CEIL(number)Example
SELECT CEIL(4.2);
-- 5Key points
- Always rounds toward positive infinity
- Aliased as CEILING() in some databases
- SQL Server only has CEILING(), not CEIL()
- CEIL(-4.8) is -4 — 'up' means towards +infinity, not away from zero
- Divide by a decimal (qty / 6.0) or integer division loses the remainder first