SQL ROUND Function: Decimals, Ties, CEIL, FLOOR and Money
SQL ROUND rounds a number to a chosen number of decimal places. Learn the syntax, how ties like 2.5 are handled, negative digits, the floating point trap behind ROUND(2.675, 2), CEIL, FLOOR and truncation, and why rounding each line differs from rounding the total, with every query run.
- SQL ROUND(x, d) rounds x to d decimal places; without d it rounds to a whole number.
- Most databases round ties away from zero: 2.5 becomes 3 and -2.5 becomes -3. Python’s
round()uses banker’s rounding instead. ROUND(2.675, 2)returned 2.67, not 2.68, because 2.675 cannot be stored exactly as a float. Use DECIMAL columns for money.- Rounding each line and then summing gave 65.27; rounding the total gave 65.28. Decide which one your invoices need.
Rounding sounds trivial until an invoice total is one cent off, a report disagrees with a spreadsheet, or 2.675 rounds down. The SQL ROUND function is simple to call, but the result depends on how ties are handled, how numbers are stored and where in the calculation you round. This guide shows each of those effects with real queries.
All queries ran on SQLite 3.50.4 and the output is copied from the run. We point out where SQL Server, PostgreSQL, MySQL and Oracle behave differently, based on their documentation. Related: our SQL CAST guide covers truncation when converting types.
SQL ROUND syntax
ROUND(number) -- round to a whole number
ROUND(number, decimals) -- round to this many decimal places
decimals is usually a small non-negative integer. In most databases it can also be negative, which rounds to tens, hundreds and so on, though not in SQLite, as we show below. SQL Server also accepts a third argument that switches to truncation.
Basic rounding
SELECT ROUND(3.14159, 2) AS two_dp, ROUND(3.14159) AS no_dp,
ROUND(3.7) AS up, ROUND(-3.7) AS neg;
-- two_dp | no_dp | up | neg
-- 3.14 | 3.0 | 4.0 | -4.0
Rounding to two places kept 3.14; rounding without a second argument gave whole numbers. In SQLite, ROUND always returns a floating-point value, so whole results print as 3.0 and 4.0. Negative numbers round symmetrically: -3.7 becomes -4.
How ties like 2.5 are rounded
SELECT ROUND(0.5) AS a, ROUND(1.5) AS b, ROUND(2.5) AS c, ROUND(-2.5) AS d;
-- a | b | c | d
-- 1.0 | 2.0 | 3.0 | -3.0
A value exactly halfway between two candidates is a tie. SQLite, like SQL Server, MySQL (for exact numbers), PostgreSQL (for numeric) and Oracle, rounds ties away from zero: 0.5 up to 1, 2.5 up to 3, -2.5 down to -3. This is sometimes called commercial rounding.
Programming languages often differ. Python’s built-in round(2.5) returned 2, because Python uses round half to even, also called banker’s rounding, which sends ties to the nearest even digit to avoid a systematic upward bias in large sums. Some databases offer this mode too, for example Snowflake’s 'HALF_TO_EVEN' option. When numbers flow between your application and database, decide on one rule, or totals will disagree by a cent here and there.
The ROUND(2.675, 2) floating point trap
SELECT ROUND(2.675, 2) AS float_trap;
-- 2.67
Surely 2.675 is a tie and should become 2.68? The problem is storage. 2.675 cannot be represented exactly in binary floating point; the closest double is
2.67499999999999982236431605997495353221893310546875
which is slightly below the halfway point, so rounding correctly gives 2.67. Python showed the same result. The same thing explains why 0.1 * 3 printed 0.30000000000000004. With an exact decimal type, the answer changes: Python’s Decimal('2.675') rounded half up gave 2.68, and a DECIMAL(10, 3) column in PostgreSQL, MySQL or SQL Server would too.
Tip: store money in DECIMAL or NUMERIC columns, or as integer cents, never in FLOAT, REAL or DOUBLE.
Negative digits: rounding to tens and hundreds
SELECT ROUND(748.58, -1) AS tens, ROUND(748.58, -2) AS hundreds;
-- SQLite: 749.0 | 749.0
-- SQL Server: 750.00 | 700.00 (from Microsoft documentation, not run)
In SQL Server, PostgreSQL, MySQL and Oracle, a negative second argument rounds to the left of the decimal point, which is handy for “about 750” style summaries. SQLite does not support this: it treated the negative value as zero and returned 749. Our test caught a portability bug that would have silently changed a report. In SQLite, round to tens with ROUND(x / 10.0) * 10 instead.
CEIL, FLOOR and truncation
SELECT CEIL(4.1) AS ceil_pos, FLOOR(4.9) AS floor_pos,
CEIL(-4.1) AS ceil_neg, FLOOR(-4.1) AS floor_neg,
CAST(-4.9 AS INTEGER) AS trunc_neg;
-- ceil_pos | floor_pos | ceil_neg | floor_neg | trunc_neg
-- 5.0 | 4.0 | -4.0 | -5.0 | -4
| Function | Direction | 4.9 | -4.1 |
|---|---|---|---|
ROUND |
Nearest | 5 | -4 |
CEIL / CEILING |
Up, toward +infinity | 5 | -4 |
FLOOR |
Down, toward -infinity | 4 | -5 |
| Truncate | Toward zero | 4 | -4 |
Use CEIL for “pages needed” or “boxes needed”, where any remainder needs one more; FLOOR for “complete units”; truncation for dropping decimals. Truncation is TRUNC in PostgreSQL and Oracle, TRUNCATE(x, d) in MySQL, ROUND(x, d, 1) in SQL Server, and a CAST to integer in SQLite. SQLite’s CEIL and FLOOR need version 3.35 or later with math functions enabled, as in our build.
Rounding money: per line or on the total?
CREATE TABLE li (price REAL, qty INT);
INSERT INTO li VALUES (19.99, 3), (5.005, 1), (0.1, 3);
SELECT ROUND(SUM(price * qty), 2) AS round_total,
SUM(ROUND(price * qty, 2)) AS sum_rounded
FROM li;
-- round_total | sum_rounded
-- 65.28 | 65.27
Same three lines, two different totals. Rounding once at the end kept the half cent from 5.005 until the final step; rounding each line first dropped it early (and the float 5.005 is really slightly less than 5.005). Neither is wrong in itself, but an invoice must use the same rule as the printed line items, otherwise the lines do not add up to the total. Tax rules in many countries specify which approach to use, so check them. For cumulative figures, see running totals in SQL.
The integer cents approach
SELECT SUM(CAST(ROUND(price * 100) AS INTEGER) * qty) / 100.0 AS via_cents FROM li;
-- 65.28
Converting prices to whole cents (or paise) and doing all arithmetic on integers avoids floating point drift entirely; divide by 100 only for display. Payment providers commonly use this representation for amounts in their APIs.
Rounding vs formatting
SELECT typeof(ROUND(3.7)) AS t, ROUND(10.0 / 3, 2) AS third,
printf('%.2f', 10.0 / 3) AS formatted;
-- real | 3.33 | 3.33
ROUND changes the number; formatting changes only how it is shown. To display trailing zeros, such as 20.00, you need a formatting function: printf in SQLite, FORMAT in SQL Server and MySQL, TO_CHAR in PostgreSQL and Oracle. Do calculations on numbers and formatting at the very end, ideally in the application. Our SQL string functions guide covers more formatting tools.
ROUND across databases
| Database | Ties | Negative digits | Truncate |
|---|---|---|---|
| SQLite (run) | Away from zero | Treated as 0 | CAST(x AS INTEGER) |
| SQL Server | Away from zero | Supported | ROUND(x, d, 1) |
| PostgreSQL | Away from zero for numeric | Supported | TRUNC(x, d) |
| MySQL | Away from zero for exact values | Supported | TRUNCATE(x, d) |
| Oracle | Away from zero for NUMBER | Supported | TRUNC(x, d) |
Only the SQLite row was run. For floating point types, PostgreSQL and MySQL may follow the platform’s rounding, which is typically half to even, another reason to keep money in exact types.
Practical rounding rules
- Store money as
DECIMALor integer cents. - Round as late as possible, and once.
- Agree a tie rule between database and application code.
- Use
CEILandFLOORfor counts of containers or complete units. - Do not rely on negative digits in SQLite.
- Format for display separately from rounding for storage.
- Remember that
ROUND(NULL)is NULL; use COALESCE for defaults.
Rounding in reports and dashboards
Percentages are a classic source of confusion: three shares of 33.33 percent each add up to 99.99, not 100. When a report must sum to exactly 100, round all values, then add the leftover difference to the largest item, a method often called largest remainder. Averages deserve the same care: average the raw values and round the result, rather than averaging already rounded numbers, which compounds the error. The same applies to group totals built with GROUP BY: aggregate first, round last. Keep full precision in the database and in intermediate calculations, round only in the final SELECT that feeds the report, and state the rounding rule in the report footer. Finance and tax teams will usually have a documented rule, so ask for it before building the query rather than discovering a one cent difference during an audit.
Final thoughts
SQL ROUND is easy to call and easy to get subtly wrong. Know your database’s tie rule, store exact values in decimal types so 2.675 really is 2.675, choose deliberately between rounding lines and rounding totals, and use CEIL, FLOOR or truncation when you mean a direction rather than “nearest”. With those habits, your reports, invoices and spreadsheets will agree to the cent.
Frequently asked questions
How do I round to 2 decimal places in SQL?
Use ROUND(value, 2). It returns the value rounded to two decimal places. Use a formatting function as well if you need to display trailing zeros such as 20.00.
Why does ROUND(2.675, 2) return 2.67?
2.675 cannot be stored exactly as a floating point number. The stored value is slightly below 2.675, so it rounds down. Exact DECIMAL columns round it to 2.68.
Does SQL round 0.5 up or down?
Most databases round ties away from zero, so 0.5 becomes 1 and minus 0.5 becomes minus 1. Python and some systems use round half to even instead, which rounds ties to the nearest even number.
What is the difference between ROUND, CEIL and FLOOR?
ROUND goes to the nearest value, CEIL always rounds up toward positive infinity, and FLOOR always rounds down toward negative infinity.
How do I round down without rounding in SQL?
Use FLOOR for positive numbers, or truncate with TRUNC in PostgreSQL and Oracle, TRUNCATE in MySQL, or ROUND with a third argument of 1 in SQL Server.
Keep learning
Need help with testing, WordPress or SEO?
We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.