ROUND / FLOOR / CEIL
Control numeric precision and bucketing. ROUND(x, n) rounds to n decimal places (negative n rounds to tens/hundreds). FLOOR and CEIL round toward −∞ and +∞ respectively; they always move the value, unlike ROUND which can stay the same.
Syntax #
SELECT
ROUND(x, n), -- nearest, ties engine-dependent
FLOOR(x), -- down, toward -inf
CEIL(x) -- up, toward +inf
FROM table_name;Example #
ROUND on .5 is not what most people think. The tie-break rule depends on the engine and the data type: DuckDB, Postgres, and MySQL round DECIMAL/NUMERIC ties away from zero (2.5 → 3), but Postgres ROUND on DOUBLE PRECISION is platform-dependent (usually half-to-even, 2.5 → 2), and DuckDB ships ROUND_EVEN for explicit banker rounding. For financial reporting or audits where totals must match to the cent, pick an explicit strategy (multiply, floor, divide) rather than trusting the default. And don't use FLOAT / DOUBLE for money: use DECIMAL(p,s) to avoid binary floating-point drift.