Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT CEIL(25 / 10.0) AS pages;   -- 3

Try it

SQL Rounding up and down, including negatives

Press Run (or Ctrl + Enter) to see the result here.

SQL Pages and price buckets ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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
-- 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, and CEIL(-2.1) is -2. To drop the decimals toward zero, use CAST(x AS INTEGER).
  • Integer division comes first. CEIL(25 / 10) is 2 in SQLite and SQL Server because 25 / 10 is already 2. Make one side a decimal (10.0) to get 3.
  • Ceiling division without the function: (a + b - 1) / b gives CEIL(a / b) for positive integers and works everywhere, including builds without ceil().
  • Bucketing: FLOOR(x / size) * size is the start of the bucket that contains x; 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.9999999999 below a whole number. Round first if exact results matter.