How does the SQL version of ROUND work?

ROUND(value, 2) keeps two decimal places, as in Excel. A negative second argument rounds to tens, hundreds and so on: ROUND(amount, -1) rounds to the nearest ten. Round only for display or at the final step; rounding each row before summing can change the total.

Why can SQL give a different result from ROUND?

On numeric values PostgreSQL rounds halves away from zero, like Excel; on floating-point values the tie-breaking can differ, so use numeric for money.

ROUND in SQL

ROUND rounds a number to a set number of decimal places; SQL has the same function, with a few differences in how ties and types are handled.

The Excel version

=ROUND(A2, 2)

The SQL equivalent

This example assumes: sales(id bigint, region text, amount numeric, vat_rate numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT id,
       amount,
       ROUND(amount * (1 + vat_rate), 2) AS amount_with_vat,
       ROUND(amount, -1) AS nearest_ten
FROM sales
ORDER BY id;

How it works

ROUND(value, 2) keeps two decimal places, as in Excel.
A negative second argument rounds to tens, hundreds and so on: ROUND(amount, -1) rounds to the nearest ten.
Round only for display or at the final step; rounding each row before summing can change the total.

Where the results differ from Excel

On numeric values PostgreSQL rounds halves away from zero, like Excel; on floating-point values the tie-breaking can differ, so use numeric for money.
Integer division truncates in some databases, so 5 / 2 is 2; Excel gives 2.5. Cast to numeric before dividing.
ROUNDUP and ROUNDDOWN map to CEIL and FLOOR for whole numbers; for decimals use TRUNC or arithmetic.

Skip the query: ask in plain English

If you would rather not write SQL, type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the result matches how your spreadsheet defines the number.

Show each sale with VAT added, rounded to 2 decimal places.
Round revenue by region to the nearest thousand.