numeric
ROUND
Rounds a numeric value to the specified number of decimal places (default 0). Uses banker's rounding (half-to-even) on Postgres numeric; half-away-from-zero on MySQL and float types.
ROUND(number [, decimals])Parameters / Modifiers
| Parameter | Purpose |
|---|---|
| decimals | Number of digits after the decimal point (may be negative to round to tens/hundreds) |
| ::numeric cast | Postgres: cast floats to numeric before ROUND for consistent rules |
| TRUNC(n, d) | Related function that chops digits instead of rounding |
Examples
SELECT ROUND(3.14159, 2);Returns 3.14
SELECT ROUND(1234.5, -2);Returns 1200 — negative decimals round to left of decimal
SELECT ROUND(2.5), ROUND(3.5);Postgres numeric: 2, 4 (banker's rounding); MySQL: 3, 4
SELECT ROUND(AVG(price)::numeric, 2) FROM products;Cast to numeric first for consistent Postgres rounding
Dialect notes / Gotcha
Rounding rules for the .5 case differ by dialect and data type — cast to NUMERIC/DECIMAL in Postgres for predictable results. ROUND(float) can surprise you due to binary representation.
Related functions
CEIL / CEILING
Returns the smallest integer greater than or equal to the value — rounding always toward positive infinity. CEIL and CEILING are aliases in every major dialect.
FLOOR
Returns the largest integer less than or equal to the value — always rounds toward negative infinity. The mirror of CEIL.
AVG
Computes the arithmetic mean of non-NULL numeric values. NULL rows are excluded from both the sum and the divisor, so AVG is not equivalent to SUM/COUNT(*) when NULLs are present.
← All SQL functions · SQL cheat sheet · Window functions · PostgreSQL cheat sheet