ROUND, CEIL and FLOOR in Queries

ROUND moves a number to the nearest representable value at a given number of decimal places. CEIL rounds towards positive infinity, so 2.1 becomes 3 and -2.1 becomes -2. FLOOR rounds towards negative infinity, so 2.9 becomes 2 and -2.9 becomes -3. CEIL and FLOOR always return an integer; ROUND takes a precision argument and keeps the type of its input. Not every SQLite build compiles the CEIL and FLOOR functions in, so the sample below works them out with a cast and a case instead.

-- ROUND is built into every SQLite build and takes a precision argument.
SELECT ROUND(2.5) AS round_half_up,
       ROUND(-2.5) AS round_negative,
       ROUND(2.345, 2) AS round_to_two_places;

-- CEIL and FLOOR are not compiled into every SQLite build, so the sample
-- spells them out. A cast to INTEGER truncates toward zero, so the sign of
-- the remainder decides which way to go: up for a positive fraction, down
-- for a negative one.
WITH vals(v) AS (VALUES (2.1), (-2.1), (2.9), (-2.9))
SELECT v,
       CAST(v AS INTEGER) + CASE WHEN v > CAST(v AS INTEGER) THEN 1 ELSE 0 END AS ceil_v,
       CAST(v AS INTEGER) - CASE WHEN v < CAST(v AS INTEGER) THEN 1 ELSE 0 END AS floor_v
FROM vals
ORDER BY v;

The results are the key part. CEIL always moves towards positive infinity regardless of sign, and FLOOR always moves towards negative infinity, which is the opposite of what "ceil" and "floor" intuitively suggest for negative numbers. If you want the magnitude rounded and the sign preserved, use ROUND, not CEIL or FLOOR.

ROUND has its own convention, and it differs between engines. SQLite's ROUND(2.5) returns 3.0 — a round-half-away-from-zero rule. PostgreSQL's round(numeric, int) also rounds half away from zero, but round(double precision) uses the C library's banker's rounding, where 2.5 becomes 2.0 and 3.5 becomes 4.0. MySQL's ROUND for exact-value numbers rounds half away from zero, while ROUND on a floating-point column uses the platform's rounding mode.

That inconsistency is why you should always specify the number of decimal places and never rely on the default: ROUND(x, 2) rather than ROUND(x). And when the value is money, cast to a fixed-precision decimal type first — ROUND(CAST(amount AS DECIMAL(12, 2)), 2) — so the function operates on an exact numeric value instead of a binary float.

Because most decimal fractions have no exact binary representation. 0.1 is stored as a repeating fraction in binary, so adding it ten times does not give exactly 1.0 — it gives something very close to 1.0 but not quite. The same effect shows up in aggregate reports: a SUM of many prices can end in 0.29999999999999993 instead of 0.30, and after ROUND you may still see values that look wrong.

The consequence is not cosmetic. A financial system that compares balances with = can reject a transaction that should succeed, because the two sides were computed by different routes and land on different binary values. Reconciliation between two systems that store the same amount differently will eventually fail, and the failure looks random.

The fix is to store money as integers in the smallest unit. Instead of a column holding 19.99, hold 1999 and call it amount_cents. Every operation becomes exact integer arithmetic: addition, subtraction, multiplication by an integer quantity. Rounding only happens at the point of display, where you divide by 100 and round deliberately. This is what payment processors generally do, and it is why the column name in the example above carries its unit.

If you must keep decimal columns, use a fixed-precision decimal type rather than a float. DECIMAL(12, 2) stores exactly two decimal places and does exact arithmetic, but the cost is that arithmetic on decimals is slower and the column cannot be indexed as efficiently in some engines.

Two related rules. Never compare money with = in application code; compare either the integer representation or the rounded decimal. And never multiply a float amount by a float quantity — convert to integers first, then multiply. SQLite is a special case worth flagging: it has no dedicated decimal type, so a column declared DECIMAL(12, 2) actually stores a REAL under the hood. SQLite is excellent for the playground and for local caching, but money belongs in a system with real decimal semantics.

Rounding for display is a projection concern: you keep the precise value in the data and produce a rounded copy alongside it. Always specify the number of decimal places, and cast to a numeric type first so the rounding is predictable.

CREATE TABLE invoices (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount_cents INTEGER NOT NULL
);

INSERT INTO invoices (id, customer_id, amount_cents) VALUES
  (1, 1, 1999),
  (2, 1, 7500),
  (3, 2, 12350),
  (4, 2, 999),
  (5, 3, 10050);

SELECT id,
       customer_id,
       amount_cents,
       ROUND(CAST(amount_cents AS REAL) / 100.0, 2) AS amount_exact,
       CAST(ROUND(CAST(amount_cents AS REAL) / 100.0) AS INTEGER) AS amount_whole
FROM invoices
ORDER BY id;

Five rows come back. The amounts are 19.99, 75.00, 123.50, 9.99 and 100.50, and the whole-unit column gives 20, 75, 124, 10 and 101. Note the two rounding behaviours side by side: 123.50 rounds up to 124 and 100.50 rounds up to 101, while 9.99 goes to 10 and 75.00 stays at 75.

The design point is that the cents column remains the source of truth. amount_exact is derived for reporting and amount_whole is the display version, but nothing writes back, so no rounding error can accumulate. This is the same argument as storing integers in the first place — the precise value lives in the database and rounding happens only where a human reads it.

Two cautions. The CAST to REAL is needed because SQLite has no fixed-point division; the division by 100.0 is what introduces the float, and rounding immediately afterwards removes the imprecision from the displayed value. And if the report needs to round a column that already contains a decimal type, cast it to DECIMAL rather than REAL so the engine does the arithmetic exactly. Also remember that SQLite's ROUND rounds halves away from zero, whereas some engines use banker's rounding, so write a test that pins the behaviour if the boundary case matters to you.