skip to content

questions

6

In SQL, what do ROUND, TRUNC, CEIL and FLOOR each return for 2.7 and for -2.7, and why?

level: juniorimportance: must knowfreq 55%

answer

  1. one direction rule per function
  2. negatives separate the four
  3. toward zero versus downward
  4. TRUNC is not spelled the same everywhere

basics

~10 s

ROUND goes to the nearest integer (3, -3), TRUNC drops the fraction toward zero (2, -2), CEIL moves up toward positive infinity (3, -2), and FLOOR moves down toward negative infinity (2, -3).

solid answer

~30 s

Each function has one direction rule, and negative values are where the rules separate. `ROUND` goes to the nearest value, `TRUNC` goes toward zero, `CEIL` goes up to the next integer and `FLOOR` goes down, so `CEIL(-2.7)` is -2 and `FLOOR(-2.7)` is -3. `ROUND` and `TRUNC` usually take a digit count too: `ROUND(2.567, 1)` is 2.6 and `TRUNC(2.567, 1)` is 2.5. The names are widely available but not universal: MySQL writes `TRUNCATE(x, d)`, and SQL Server truncates with `ROUND(x, d, 1)`, names its ceiling function `CEILING`, and requires the digit argument on `ROUND`.

code

sql · 5 lines
sql
SELECT round(-2.7), trunc(-2.7), ceil(-2.7), floor(-2.7);
-- -3 | -2 | -2 | -3

SELECT round(42.4382, 2), trunc(42.4382, 2);
-- 42.44 | 42.43

go deeper

for a junior

Recall the four directions and state the -2.7 results without hesitating: -3, -2, -2, -3. Say toward zero for TRUNC and down for FLOOR rather than describing both as removing decimals.

for a middle

Explain why TRUNC agrees with FLOOR on positives and with CEIL on negatives, and show where it matters by bucketing negative readings. Mention that the digit argument belongs to ROUND and TRUNC.

for a senior

Show the habit of checking the argument first: a correct CEIL over an already truncated integer quotient is still wrong. Call out the spelling differences before a query moves between engines.

for a principal

Treat rounding direction as a visible choice in shared query code. When a team targets more than one engine, name the truncation and ceiling spellings in its conventions so a port cannot quietly change results.

## One direction rule per function All four functions turn a number with a fraction into a simpler number. Each follows one fixed rule about which way to move along the number line, and that rule does not change with the sign of the input. What changes is how the rule looks to someone thinking in terms of "bigger" and "smaller". | Function | Rule | `2.7` | `-2.7` | |---|---|---|---| | `ROUND(x)` | nearest integer | 3 | -3 | | `TRUNC(x)` | drop the fraction, moving toward zero | 2 | -2 | | `CEIL(x)` | smallest integer not below `x` | 3 | -2 | | `FLOOR(x)` | largest integer not above `x` | 2 | -3 | Two pairs are worth memorising, because each pair agrees on one side of zero and disagrees on the other: - **Positive input:** `TRUNC` and `FLOOR` agree, which is why people assume they are the same function. - **Negative input:** `TRUNC` and `CEIL` agree, because moving toward zero is moving upward. - **Any input that is already whole:** all four return it unchanged. ## The digit argument `ROUND` and `TRUNC` commonly accept a second argument, the number of decimal places to keep. `CEIL` and `FLOOR` generally do not, and they always land on a whole number. - `ROUND(42.4382, 2)` is 42.44 and `TRUNC(42.4382, 2)` is 42.43. - A negative digit count works to the left of the point on most engines: PostgreSQL returns 1230 for `ROUND(1234.56, -1)`. - SQLite treats a negative digit count as zero, so the same call returns 1235.0 there. - To take a ceiling at two decimals, scale by hand: `CEIL(x * 100) / 100`, on a value that is not an integer type. On PostgreSQL the two-argument forms of `round` and `trunc` are defined for `numeric` only, so a `double precision` value has to be cast first. ## Spelling is not portable These names are widely available, but do not assume any one of them works on every engine. | Engine | Truncate | Ceiling | `ROUND` with one argument | |---|---|---|---| | PostgreSQL | `TRUNC(x)` or `TRUNC(x, d)` | `CEIL` or `CEILING` | allowed | | MySQL | `TRUNCATE(x, d)`, `d` required | `CEIL` or `CEILING` | allowed | | SQL Server | `ROUND(x, d, 1)` | `CEILING` only | error, `d` required | | Oracle | `TRUNC(x)` or `TRUNC(x, d)` | `CEIL` only | allowed | | SQLite | `trunc(x)` | `ceil` or `ceiling` | allowed | SQLite's `trunc`, `ceil` and `floor` exist only in builds with its math functions compiled in, so check before relying on them. `ROUND` and `FLOOR` are the two names these five share. ## Where the difference changes a result Two everyday queries depend on picking the right function. 1. **Bucketing values that can be negative.** To group readings into bands ten wide, `FLOOR(reading / 10.0) * 10` sends -3 to the band starting at -10 and 3 to the band starting at 0. Using `TRUNC` sends both to 0, so that one band is twice as wide as the others and the chart is silently wrong. 2. **Counting pages.** Rows divided by page size must round up: `CEIL(25 / 10.0)` is 3. Written as `CEIL(25 / 10)` on an engine that divides integers, the division happens first and yields 2, and `CEIL(2)` is still 2. The function was right and its input was already truncated. The second case is the reason to read an expression from the inside out: `ROUND`, `TRUNC`, `CEIL` and `FLOOR` only act on the value they receive. ## Return type and NULL - All four return `NULL` for a `NULL` input, like most scalar functions. - The result is often a numeric type rather than an integer type. PostgreSQL's `ceil(numeric)` returns `numeric`, so a value that prints as `3` is not an `integer`. - Display can differ too: SQL Server keeps the input's scale and shows `ROUND(2.567, 1)` as 2.600, where PostgreSQL shows 2.6. ## Common wrong predictions - Expecting `CEIL(-2.7)` to be -3, by treating "ceiling" as "further from zero". - Expecting `FLOOR(-2.7)` to be -2, by treating "floor" as "remove the decimals". - Using a `CAST` to an integer type as if it were `TRUNC`. Engines differ: PostgreSQL rounds 2.7 to 3 on the cast, while SQL Server and SQLite truncate it to 2. What `ROUND` does at an exact half, such as 2.5, is a separate question, because it depends on whether the value has an exact or an approximate type.

  • Why does CEIL(25 / 10) return 2 rather than 3 on an engine that divides integers?
    The argument is evaluated first: `25 / 10` is integer division and yields 2, so `CEIL` receives a whole number and returns it. Make the division exact, `CEIL(25 / 10.0)`, and the result is 3. Integer arithmetic also works: `(25 + 10 - 1) / 10`, when both values are positive integers.
  • Does CAST(2.7 AS INTEGER) behave like ROUND or like TRUNC?
    It depends on the engine. PostgreSQL rounds on that cast and returns 3, while SQL Server and SQLite truncate and return 2. A query that needs one specific behaviour should call `ROUND` or the engine's truncation function explicitly and cast afterwards, so the choice is visible and survives a move between engines.
  • How do you put readings into bands ten wide when some readings are negative?
    Use `FLOOR(reading / 10.0) * 10`. `FLOOR` moves every value down, so -3 lands in the band starting at -10 and every band has the same width. `TRUNC` moves toward zero, so -3 and 3 share a band starting at 0, which is then twice as wide as its neighbours.

Picture a building with basement levels, where -2.7 sits between level -2 and level -3. The ceiling above you is -2 and the floor beneath you is -3, TRUNC rides toward ground level to -2, and ROUND steps to the nearest level, -3.

  • CEIL always moves a number further from zero, so CEIL(-2.7) is -3.
  • FLOOR just removes the decimals, so FLOOR(-2.7) is -2.
  • TRUNC and FLOOR are the same function under two names.
  • TRUNC is spelled the same way on every SQL engine.
  • ROUND with one argument is accepted on every SQL engine.
open as a page

In SQL, what do SELECT 7 / 2 and SELECT -7 / 2 return on an integer-dividing engine, and which common engines differ?

level: middleimportance: must knowfreq 60%

basics

~20 s

An integer-dividing engine returns 3 and -3, because the quotient truncates toward zero. PostgreSQL, SQL Server and SQLite divide integers that way; MySQL and Oracle return a fractional result, 3.5 and -3.5, from the same expression.

open as a page

In SQL, why can ROUND(2.5) return 3 on a DECIMAL value but 2 on a FLOAT value?

level: middleimportance: should knowfreq 42%

basics

~20 s

Exact types such as DECIMAL commonly break a tie away from zero, giving 3, while approximate types often round ties to the nearest even number, giving 2. The float rule varies by engine and platform, so it is not portable.

open as a page

In SQL, what does MOD(-7, 3) return, and why does WHERE MOD(x, 2) = 1 miss some odd numbers?

level: middleimportance: should knowfreq 38%

basics

~20 s

MOD(-7, 3) returns -1 because the remainder takes the sign of the dividend on the common engines. A negative odd x gives MOD(x, 2) = -1, so the test misses it; write MOD(x, 2) <> 0 instead.

open as a page

A revenue report's totals miss finance's by a few cents and its conversion-rate column is always 0; how do you diagnose and fix both in SQL?

level: seniorimportance: should knowfreq 46%

basics

~20 s

The always-0 column is integer division: cast one operand to DECIMAL before dividing and guard the denominator with NULLIF. The cents usually come from rounding: compare your rounding stage, tie rule and any truncation with finance's on one mismatched document.

open as a page

In SQL, what precision and scale does DECIMAL arithmetic return, and why does 1.0 / 3 show different digits on each engine?

level: middleimportance: nice to knowfreq 30%

basics

~20 s

Addition keeps the larger operand scale and multiplication adds the scales on most engines. Division is often inexact, so each engine picks its own result scale, and 1.0 / 3 can show 20, 5 or 6 decimals.

open as a page