CEIL / CEILING in SQL: Rounding Up to the Next Integer

CEIL (also spelled CEILING) rounds a number up to the nearest integer that is not smaller than the input. It is the workhorse for pagination page counts, bucketing data, and any "how many containers do I need to hold N items" logic.

Basic behavior

CEIL always moves toward positive infinity. Whole numbers stay as they are; any fractional part pulls the result up.

SELECT
  CEIL(4.1)  AS a,   -- 5
  CEIL(4.0)  AS b,   -- 4
  CEIL(-4.1) AS c,   -- -4  (toward zero, not away)
  CEILING(7.0001) AS d; -- 8

Key points:

• CEIL and CEILING are exact synonyms in every major database.
• For negative numbers, "up" means toward zero: CEIL(-4.1) is -4, not -5.
• In PostgreSQL the result over numeric stays numeric, and over double precision stays double precision. The fractional part is always dropped.

Pagination page counts

The classic case: you have total rows and per_page per page, so how many pages are there? Integer division truncates the remainder, so you need CEIL.

SELECT
  COUNT(*) AS total_orders,
  CEIL(COUNT(*)::numeric / 25) AS total_pages
FROM orders
WHERE status = 'paid';

The same trick per country, for how many list screens are needed to show all users at 20 per page:

SELECT
  country,
  COUNT(*) AS users,
  CEIL(COUNT(*)::numeric / 20) AS pages
FROM users
GROUP BY country
ORDER BY users DESC;

Gotcha: integer division. In PostgreSQL 5 / 2 is 2 (integer), and CEIL(5 / 2) returns 2, not 3. The division already happened before CEIL ran. Always cast at least one operand to a fractional type: CEIL(5::numeric / 2) or CEIL(5.0 / 2). This bug is sneaky because on "clean" data (when total is a multiple of per_page) the result matches the correct one, and the off-by-one only surfaces on the last partial page.

Bucketing and range binning

CEIL is great at mapping continuous values into discrete, fixed-width buckets. Group employees into salary bands of width 10000:

SELECT
  CEIL(salary / 10000.0) * 10000 AS salary_band,
  COUNT(*) AS people
FROM employees
GROUP BY CEIL(salary / 10000.0)
ORDER BY salary_band;

An employee earning 41000 lands in the 50000 band (the upper edge of the 40001 to 50000 range). If you want the lower edge, use FLOOR. You can slice order amounts into price segments the same way:

SELECT
  CEIL(amount / 100.0) AS price_bucket,
  COUNT(*) AS orders
FROM orders
GROUP BY CEIL(amount / 100.0)
ORDER BY price_bucket;

CEIL vs FLOOR and ROUND

Three functions, three different directions:

• CEIL(x) rounds up toward positive infinity: CEIL(2.1) = 3, CEIL(2.9) = 3.
• FLOOR(x) rounds down toward negative infinity: FLOOR(2.9) = 2, FLOOR(-2.1) = -3.
• ROUND(x) rounds to the nearest integer: ROUND(2.4) = 2, ROUND(2.5) = 3.

SELECT
  x,
  CEIL(x)  AS up,
  FLOOR(x) AS down,
  ROUND(x) AS nearest
FROM (VALUES (2.1), (2.5), (2.9), (-2.5)) AS t(x);

For pagination and "how many containers" math, reach only for CEIL: even a single extra row needs another whole page, and ROUND would be wrong exactly at the halfway mark.

Database differences

• PostgreSQL and MySQL: CEIL and CEILING behave the same; in MySQL also watch out for integer division and use / with fractional literals or a cast.
• ClickHouse: the function is ceil and accepts a second precision argument: ceil(4.123, 1) is 4.2. That is handy for rounding up to tenths or hundredths, which the standard CEIL cannot do.
• In every system CEIL(NULL) returns NULL, so account for that in aggregates and COALESCE.

-- ClickHouse: round up to one decimal place
SELECT ceil(4.123, 1) AS r;  -- 4.2