SQL ROUND: Rounding to the Nearest Integer, Banker's Rounding and Money

ROUND rounds a number to the nearest integer. That sounds trivial, but the function hides two different strategies for handling halves and a sneaky difference between the exact numeric type and the approximate double precision, the kind of difference that quietly breaks financial reports.

Basic rounding to an integer

With a single argument, ROUND drops the fractional part and snaps to the nearest integer. Halves (.5) go away from zero: 2.5 becomes 3, and -2.5 becomes -3.

SELECT
  ROUND(3.14159) AS a,   -- 3
  ROUND(2.5)     AS b,   -- 3
  ROUND(3.5)     AS c,   -- 4
  ROUND(-2.5)    AS d;   -- -3

Key properties:

• For numeric the result keeps the same type, the fractional part is simply zeroed.
• The strategy for literals like 2.5 is half-away-from-zero, symmetric around zero.
• To keep decimal places, there is a second argument ROUND(x, n), covered in a separate article.

numeric vs double: where banker's rounding hides

The big gotcha is that PostgreSQL rounds halves differently depending on the type. The literal 2.5 is numeric and goes away from zero. But double precision uses banker's rounding (round-half-to-even): halves snap to the nearest even integer.

SELECT
  ROUND(2.5::numeric)          AS num_25,   -- 3
  ROUND(3.5::numeric)          AS num_35,   -- 4
  ROUND(2.5::double precision) AS dbl_25,   -- 2
  ROUND(3.5::double precision) AS dbl_35;   -- 4

For double, both 2.5 and 3.5 round to 2 and 4 because the even neighbor wins under half-to-even. This is not a bug, it is a way to cancel out the systematic bias you get when summing many rounded values.

Gotcha: the very same number gives a different answer purely because of its type. If your query mixes constants and float columns, the totals can be off by a cent. Cast to numeric explicitly when predictability matters.

Rounding money

For money, double is dangerous for a second reason: not every decimal fraction is representable in binary floating point. Compare an accumulated total:

SELECT
  SUM(amount)            AS raw_total,
  ROUND(SUM(amount))     AS rounded_total
FROM orders
WHERE status = 'paid';

If amount is declared numeric(12,2), everything is exact. If it is double precision, you may already carry tails like 19.999999998. The rule is simple: store money as numeric and apply ROUND to numeric.

Round the average salary per department to an integer:

SELECT
  dept,
  ROUND(AVG(salary)) AS avg_salary
FROM employees
GROUP BY dept
ORDER BY avg_salary DESC;

AVG over a numeric column returns numeric, so the rounding here predictably uses half-away-from-zero.

MySQL and ClickHouse differences

ROUND exists everywhere, but the halves strategy differs.

• MySQL: ROUND(x) for exact types (DECIMAL) rounds away from zero, while for approximate types (DOUBLE) the behavior depends on the underlying C library, usually banker's rounding. The same split as PostgreSQL.

SELECT ROUND(2.5), ROUND(2.5e0);
-- 3 (DECIMAL, away from zero), often 2 (DOUBLE, to even)

• ClickHouse: round() defaults to banker's rounding, to the nearest even integer. If you want the familiar away-from-zero behavior, use roundBankers() for clarity or dedicated functions like floor/ceil.

SELECT round(2.5), round(3.5), roundBankers(2.5);
-- 2, 4, 2

Remember one thing: "round to an integer" is not a single operation but a family of strategies. Before you trust ROUND with a financial report, check the argument's type and how your database treats exact halves.