SQL ROUND(x, n): Rounding to Decimal Places, Negative n, and Currency Formatting

ROUND(x, n) rounds the number x to n decimal places. It sounds trivial, but this is exactly where people get bitten: in PostgreSQL the two-argument form only works with numeric, not double precision, and the query fails out of nowhere.

What ROUND(x, n) does

The first argument is the number, the second is how many decimal places to keep. It rounds to the nearest value; ties go away from zero for numeric in PostgreSQL.

SELECT
  ROUND(3.14159, 2) AS pi2,   -- 3.14
  ROUND(3.14159, 0) AS pi0,   -- 3
  ROUND(2.5,     0) AS half;  -- 3

Key properties:

• n defaults to 0, so ROUND(x) rounds to a whole number.
• The result keeps the numeric type and the requested scale: ROUND(2, 2) returns 2.00, not 2.
• It rounds, it does not truncate: TRUNC(3.99, 1) gives 3.9, while ROUND(3.99, 1) gives 4.0.

Negative n: rounding left of the decimal point

The most underrated feature is a negative n. It rounds to the left of the decimal point: to tens, hundreds, thousands. That is handy for grouping amounts into buckets.

SELECT
  ROUND(12345.678, -1) AS tens,       -- 12350
  ROUND(12345.678, -2) AS hundreds,   -- 12300
  ROUND(12345.678, -3) AS thousands;  -- 12000

In practice it lets you build a histogram of order amounts without a separate range table:

SELECT
  ROUND(amount, -2) AS bucket,
  COUNT(*)          AS orders_in_bucket
FROM orders
WHERE status = 'paid'
GROUP BY ROUND(amount, -2)
ORDER BY bucket;

Every order falls into a bucket 100 wide, and you instantly see the distribution of ticket sizes.

numeric vs double precision: the big gotcha

Here is the real stumbling block. PostgreSQL has round(numeric, integer) but no round(double precision, integer). If the column is double precision or real, the two-argument call cannot find a matching function:

-- ERROR: function round(double precision, integer) does not exist
SELECT ROUND(salary / 12.0, 2) FROM employees;

Here salary / 12.0 yields double precision. The fix is a cast to numeric:

SELECT
  name,
  ROUND(salary::numeric / 12, 2) AS monthly_pay
FROM employees;

The nasty part is that the one-argument ROUND(double precision) does exist and works, so the error stays hidden until someone adds a second argument. Simple rule: store money as numeric(12, 2) from the start, and you will never need the cast.

Formatting currency

ROUND brings the number itself to the right scale, but it does not add a currency sign, group thousands, or keep trailing zeros on a double precision. For a pretty output, reach for to_char.

SELECT
  u.name,
  SUM(o.amount)                              AS raw_total,
  ROUND(SUM(o.amount), 2)                     AS rounded_total,
  to_char(ROUND(SUM(o.amount), 2), 'FM999G999D00') AS pretty_total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid'
GROUP BY u.name;

The mask FM999G999D00 strips padding spaces (FM), adds a thousands separator (G), and pins two decimal places (D00). Round for math, format with to_char for display: do not mix the two jobs in one expression.

MySQL and ClickHouse differences

The idea of ROUND(x, n) is the same everywhere, but the details matter.

• MySQL accepts ROUND on both integer and decimal types without a cast, and a negative n works too. For a grouped money format, use FORMAT(x, 2).

SELECT FORMAT(ROUND(amount, 2), 2) AS pretty_total
FROM orders;

• ClickHouse separates banker's rounding from the usual kind: round rounds half to even, while roundBankers makes it explicit. If you expect the familiar "half up", keep this difference in mind.

SELECT
  round(2.5)        AS r,   -- 2 (half to even)
  roundBankers(3.5) AS rb;  -- 4

Remember one thing: ROUND controls the precision of a number, not its appearance, and in PostgreSQL the second argument almost always needs numeric.