1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

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.

9 min read
SQL ROUND function: decimals, ties, CEIL, FLOOR and money

Key takeaways

  • 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 DECIMAL or integer cents.
  • Round as late as possible, and once.
  • Agree a tie rule between database and application code.
  • Use CEIL and FLOOR for 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

Work with Stack Quora

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.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

Random Forest in Python: Bagging, Feature Sampling and OOB From Scratch

Random forest in Python from scratch: bootstrap sampling, feature sampling, number of trees, out-of-bag evaluation and impurity vs permutation importance.

Read article →
Software Development

MIME Types Explained: Content-Type, nosniff and a Common Types List

MIME types explained and tested in a browser: Content-Type, nosniff, scripts and stylesheets with wrong types, a common MIME types…

Read article →
DevOps

Git Clean Explained: Remove Untracked Files Without Regret

Git clean explained with dry runs: -n, -f, -d, -x, -X, exclusions, paths, nested repositories and interactive mode, plus a…

Read article →
Test Automation

Pytest approx: Comparing Floats With rel, abs and Collections

Pytest approx explained: default tolerances, the near-zero trap, rel and abs, lists and dicts, NaN, NumPy, and how it differs…

Read article →
Software Testing

Accessibility Testing: WCAG Checklist, Automated Tools and Manual Checks

Accessibility testing explained: WCAG basics, an axe-core scan of a broken and a fixed page, what automation missed, contrast checks…

Read article →
Databases & SQL

SQL UNPIVOT: Turn Columns Into Rows, 4 Ways Tested

SQL UNPIVOT explained: turn columns into rows with UNION ALL, CROSS JOIN with CASE, JSON functions or native UNPIVOT, with…

Read article →
Software Development

Intl NumberFormat in JavaScript: Currency, Compact, Percent and Units

Intl.NumberFormat in JavaScript: locales, currency, compact notation, percent, units, rounding, formatToParts, the non-breaking space trap and formatter reuse.

Read article →
Software Development

Python Abstract Class: ABC, abstractmethod and When to Use Them

Python abstract class explained: ABC and abstractmethod, abstract properties, shared logic with super, register, collections.abc and ABC vs Protocol, tested.

Read article →
Software Development

Python Subprocess Explained: run, Capture Output, Timeouts and Safety

Python subprocess explained with tested examples: run, capture_output, check, timeouts, input, environment, shell injection, encoding on Windows and Popen streaming.

Read article →
Databases & SQL

SQL EXPLAIN Plan: How to Read Query Plans and Fix Slow Queries

SQL EXPLAIN plan explained: read scans, index searches, covering indexes and temporary sorts, with before and after timings for five…

Read article →
Artificial Intelligence

Decision Tree in Python: Gini, Entropy and Overfitting From Scratch

Decision tree in Python from scratch: Gini impurity, entropy, information gain, best splits, readable rules, and how depth and leaf…

Read article →
Software Development

URL Query Parameters: Encoding, URLSearchParams and Common Traps

URL query parameters explained: reading and building them with URLSearchParams and urllib, percent encoding, plus signs, repeated keys and nine…

Read article →
DevOps

Git Blame Explained: Find Who Changed a Line, and Why

Git blame explained with a real repository: ignore whitespace, line ranges and functions, ignore-revs files for formatting commits, moved code…

Read article →
Test Automation

Pytest Benchmark: Measure Speed and Catch Performance Regressions

Pytest benchmark tutorial: the benchmark fixture, reading results, parametrized and pedantic benchmarks, saving baselines and failing the build on regressions.

Read article →
Software Testing

Defect Density: Formula, Worked Examples and How to Use It

Defect density explained: the formula, a five-module worked example, per KLOC vs per function point, severity weighting, defect removal efficiency…

Read article →
Databases & SQL

Case Insensitive Search in SQL: LOWER, LIKE, COLLATE and Indexes

Case insensitive search in SQL: LOWER, LIKE, COLLATE NOCASE and expression indexes compared with query plans, plus accented letters and…

Read article →
Databases & SQL

ON DELETE CASCADE in SQL: CASCADE vs SET NULL vs RESTRICT

ON DELETE CASCADE in SQL tested: CASCADE vs SET NULL vs RESTRICT, multi-level cascades, the SQLite foreign key pragma, row…

Read article →
Software Development

JavaScript BigInt Explained: Big Numbers, Division and JSON

JavaScript BigInt explained: the 2^53 limit, creating and converting BigInt, division, mixing types, JSON stringify and parse without losing digits,…

Read article →
Artificial Intelligence

Logistic Regression in Python: Sigmoid, Log Loss and Gradient Descent From Scratch

Logistic regression in Python from scratch: sigmoid, log loss, gradient descent, odds ratios, thresholds, sigmoid overflow and separable data, tested…

Read article →
DevOps

Git Worktree Explained: Work on Two Branches at Once

Git worktree explained: add, list, remove, prune, lock and detached worktrees, a hotfix workflow, and the errors you will hit,…

Read article →
Test Automation

Pytest ini Explained: addopts, markers, testpaths and Precedence

Pytest ini explained: testpaths, addopts, strict markers, xfail_strict, filterwarnings, minversion, config file precedence and command-line overrides, tested.

Read article →
Software Testing

Test Estimation Techniques: WBS, Three Point PERT and Monte Carlo

Test estimation techniques worked through: WBS, three point PERT, total uncertainty, a Monte Carlo check, percentage rules and Wideband Delphi.

Read article →
Databases & SQL

Gaps and Islands in SQL: Find Streaks and Missing Ranges

Gaps and islands in SQL: find login streaks and missing ranges with ROW_NUMBER, DENSE_RANK, LAG and LEAD, plus the duplicate…

Read article →
Databases & SQL

INSERT INTO SELECT in SQL: Copy Rows Between Tables Safely

INSERT INTO SELECT in SQL: copy rows between tables with chosen columns, joins and summaries, avoid duplicates on reruns, upsert,…

Read article →
Artificial Intelligence

Linear Regression in Python: Least Squares From Scratch, With R Squared

Linear regression in Python from scratch: closed form, NumPy least squares and gradient descent, R squared, RMSE, outliers and curved…

Read article →
DevOps

Git Remote Explained: Add, Set-URL, Rename, Remove and Push URLs

Git remote explained: add, set-url, separate push URLs, rename, remove and prune, plus how to avoid leaking tokens in remote…

Read article →
Test Automation

Pytest Mock: mocker.patch, spy, side_effect and autospec Explained

Pytest mock explained: mocker.patch, return_value, side_effect, spy, autospec and PropertyMock, with tested examples for APIs, clocks and retries.

Read article →
Software Testing

System Testing Explained: Types, Process and a Real End-to-End Example

System testing explained: types, entry and exit criteria, the process, and a real end-to-end test suite run against a small…

Read article →
Artificial Intelligence

Naive Bayes Classifier Explained: Build a Spam Filter in Python

Naive Bayes classifier explained by building a spam filter in Python: Bayes rule, the naive assumption, Laplace smoothing, log probabilities…

Read article →
DevOps

Git Branch Explained: Create, Switch, Rename, Delete and Recover

Git branch explained with tested commands: list, create, switch, rename, delete, merged checks, recovering deleted branches, remote tracking and naming.

Read article →
Test Automation

Pytest raises: Testing Exceptions, match and excinfo

Pytest raises explained with tested examples: match regexes, excinfo, custom and chained exceptions, parametrized error cases, pytest.warns and common mistakes.

Read article →
Software Testing

Non Functional Testing: Types, Metrics and a Real Load Test

Non functional testing explained: 10 key types, measurable requirements, latency percentiles and what a small real load test revealed about…

Read article →
Artificial Intelligence

K Means Clustering Explained: How It Works, With Python Code

K means clustering explained: the algorithm, a from-scratch NumPy version, choosing k with the elbow and silhouette methods, k-means++, scaling…

Read article →
DevOps

Git Log Explained: 15 Useful Commands and Pretty Formats

Git log explained with 15 tested commands: oneline, graph, filters by author, date, message and code, file history, stats, pretty…

Read article →
Test Automation

Pytest conftest.py Explained: Shared Fixtures, Hooks and Overrides

Pytest conftest.py explained with a tested suite: shared fixtures, scope and teardown, folder overrides, autouse, custom options, markers and useful…

Read article →
Software Testing

Usability Testing: Methods, Metrics, SUS Score and Checklist

Usability testing explained: methods, planning, writing tasks, running sessions, metrics, SUS scoring, how many users you need and a practical…

Read article →