Recipe

Round a number to N decimal places

Short answer: ROUND(value, n) rounds to n decimal places, and works on every major engine. CEIL(value) rounds up to the next integer; FLOOR(value) rounds down; TRUNC(value, n) truncates without rounding. Watch the rounding mode: most engines use banker's rounding (round half to even) for floats and round half away from zero for decimals, so they can produce different results for the same input.

Canonical SQL #

sql

SELECT
  ROUND(123.456, 2)        AS round_2_decimals,        -- 123.46
  ROUND(123.456, 0)        AS round_to_integer,        -- 123
  ROUND(123.456, -1)       AS round_to_tens,           -- 120
  CEIL(123.456)            AS round_up,                -- 124
  FLOOR(123.456)           AS round_down,              -- 123
  TRUNC(123.456, 2)        AS truncate_2_decimals;     -- 123.45 — Postgres / Oracle / Snowflake (no 2-arg TRUNC on DuckDB / MySQL / SQL Server)

Example #

Loading SQL editor...

Common patterns #

sql

-- Round money to 2 decimals (display-ready).
SELECT ROUND(SUM(amount), 2) AS total FROM orders;

-- Percentage with 1 decimal (e.g., 23.4%).
SELECT ROUND((COUNT(*) FILTER (WHERE status = 'paid')::DECIMAL / COUNT(*)) * 100, 1) AS pct_paid
FROM   orders;

-- Round to nearest 100 (or any unit).
SELECT ROUND(price / 100) * 100 AS price_to_nearest_100 FROM products;

-- Round up to next multiple of 5.
SELECT CEIL(value / 5.0) * 5 AS next_5 FROM measurements;

-- Truncate (cut off, no rounding).
SELECT TRUNC(value, 2) FROM measurements;             -- Postgres / Oracle / Snowflake
SELECT FLOOR(value * 100) / 100 FROM measurements;    -- portable equivalent

-- Convert float to N-decimal-place DECIMAL for storage.
SELECT CAST(amount AS DECIMAL(10, 2)) FROM orders;     -- rounds during cast

Dialect notes #

Postgres: ROUND(numeric, int); only numeric types support a precision argument. ROUND(double precision) (no second arg) rounds to the nearest integer; tie-breaking is platform-dependent (usually banker's rounding). To round a DOUBLE to N places: cast first (ROUND(value::numeric, 2)).
MySQL: ROUND(value, n). Default mode is "round half away from zero" but depends on SQL_MODE. TRUNCATE(value, n) instead of TRUNC.
SQL Server: ROUND(value, n) rounds; ROUND(value, n, 1) truncates (third arg = truncate flag). CEILING (not CEIL).
Oracle: ROUND, TRUNC, CEIL, FLOOR all standard. ROUND(value, -2) for "round to nearest 100."
Snowflake: ROUND(value, n). Rounds ties away from zero by default; pass 'HALF_TO_EVEN' as the third argument for banker's rounding. TRUNC(value, n) truncates.
BigQuery: ROUND(value, n). TRUNC(value, n) truncates. Ties round away from zero by default; ROUND(x, n, 'ROUND_HALF_EVEN') on NUMERIC/BIGNUMERIC for banker's rounding.
DuckDB: ROUND(value, n), CEIL, FLOOR available; TRUNC takes no precision argument, so truncate to N places with TRUNC(value * 100) / 100. ROUND_EVEN(value, n) for banker's rounding.
SQLite: ROUND(value, n). No native TRUNC or CEIL/FLOOR until 3.35+.

Warning

**Round at the display layer, not at every step of the calculation.** ROUND(SUM(amount), 2) after the aggregation is right; SUM(ROUND(amount, 2)) per row before the aggregation can produce a total that's off by a cent on every batch (rounding errors accumulate). The pattern: keep full precision through every calculation; round only when emitting to a human-readable number. Especially relevant for money and ratios; see DECIMAL vs FLOAT for the related precision question.

Round a number to N decimal places

Canonical SQL #

Example #

Common patterns #

Dialect notes #

Related In Recipes