ROUND() Function in SQL
Introduction
ROUND is a numeric SQL function that reduces a value to a requested precision according to the database's rounding rules. It is used in prices, percentages, measurements, averages, taxes, interest, currency conversions, dashboards, and financial reports. A common call keeps two digits after the decimal point.
SELECT ROUND(123.4567, 2) AS rounded_value;
-- Common result: 123.46
The simple example hides important choices. The input data type can be exact or approximate, tie-breaking behavior can vary, negative precision can round digits to the left of the decimal point, and rounding each row before aggregation can produce a different total from rounding once after aggregation. ROUND also changes a numeric result, not necessarily its displayed number of trailing zeros.
Reliable rounding therefore begins with a business rule: what value is rounded, at which stage, to what precision, with which tie rule, and for which legal or reporting purpose? SQL should implement that policy explicitly rather than using two decimal places by habit.
Basic ROUND Syntax
Most major databases support a number and a precision argument. Some also support a one-argument form that rounds to whole-number precision.
ROUND(numeric_expression, decimal_places)
SELECT ROUND(45.6789, 2) AS rounded_value;
-- Common result: 45.68
The first argument can be a literal, column, calculation, aggregate, parameter, or another numeric expression. Return type rules differ by DBMS and input type.
Rounding to Decimal Places
A positive precision identifies digits to retain to the right of the decimal point. The next digit influences the result under the database's rounding rule.
SELECT ROUND(123.4567, 1) AS one_decimal,
ROUND(123.4567, 2) AS two_decimals,
ROUND(123.4567, 3) AS three_decimals;
-- Common results: 123.5, 123.46, 123.457
Precision describes the numeric operation. It does not guarantee that a client displays 123.50 rather than 123.5.
Rounding to Whole Numbers
A precision of zero rounds at the ones position.
SELECT ROUND(123.4567, 0) AS first_value,
ROUND(123.7567, 0) AS second_value;
-- Common results: 123 and 124
A one-argument form such as ROUND(123.67) also returns whole-number precision in databases that support that signature. Verify both syntax and return type on the target engine.
Negative Precision
Many databases accept a negative precision to round positions left of the decimal point. Minus one targets tens, minus two targets hundreds, and minus three targets thousands.
SELECT ROUND(1234.56, -1) AS nearest_ten,
ROUND(1234.56, -2) AS nearest_hundred,
ROUND(5678.00, -3) AS nearest_thousand;
-- Typical results: 1230, 1200, 6000
Negative precision is useful for summarized forecasts, capacity reporting, and privacy-preserving high-level output. It should not be used where exact amounts remain legally or operationally necessary.
Using ROUND with Columns
A query can return the stored value and a rounded result side by side.
SELECT product_name,
price AS stored_price,
ROUND(price, 2) AS rounded_price
FROM products;
| product_name | stored_price | rounded_price |
|---|---|---|
| Laptop | 899.4567 | 899.46 |
| Mouse | 25.6789 | 25.68 |
| Keyboard | 49.1234 | 49.12 |
SELECT does not modify stored prices. Use UPDATE only when the rounded form is the intended authoritative value.
ROUND Is Not Display Formatting
ROUND(12.5, 2) controls numeric precision but may still display as 12.5. Trailing zeros, currency symbols, grouping separators, and localized decimal marks are presentation concerns.
Keep the result numeric for further calculation. Apply fixed visual formatting at the final report or application boundary using DBMS-specific formatting functions or a locale-aware presentation library.
Exact and Approximate Numeric Types
DECIMAL and NUMERIC are exact decimal types with declared precision and scale. FLOAT, REAL, and DOUBLE are approximate binary types. Many familiar decimal fractions cannot be represented exactly in binary, so the stored value can be slightly above or below what was typed.
ROUND operates on the actual stored value. A surprising result may come from the approximation before ROUND is called. Use an appropriate exact type for money, tax, contractual rates, and other decimal-sensitive calculations.
Choosing DECIMAL Precision and Scale
In DECIMAL(12, 2), 12 is the total number of significant decimal digits and 2 is the maximum scale to the right of the decimal point. The remaining digits support the integer portion.
CREATE TABLE payment (
payment_id BIGINT PRIMARY KEY,
amount DECIMAL(12, 2) NOT NULL
);
Choose capacity for real maximum values, intermediate calculations, and future growth. Multiplication and division can need more precision than the final stored amount.
Tie-Breaking Rules
A tie occurs when a value is exactly halfway between two results at the requested precision. Examples include 2.5 at zero places and 1.245 at two places. Databases and numeric types do not always resolve ties identically.
Common policies include half away from zero and half to even, sometimes called banker's rounding. Approximate input may not be an exact tie at all. Test positive and negative ties on every supported database and document the required policy for regulated calculations.
Rounding Negative Values
Negative values require explicit tests because phrases such as "round up" are ambiguous. Under a nearest-value rule, -12.34 to one decimal commonly becomes -12.3, while -12.36 commonly becomes -12.4.
SELECT ROUND(-12.345, 2) AS rounded_negative;
The exact result for a halfway value depends on DBMS rules and input type. Do not infer negative behavior from positive examples alone.
NULL Handling
ROUND normally propagates NULL because there is no numeric value to round.
SELECT ROUND(NULL, 2) AS rounded_value;
-- Result: NULL
COALESCE can replace NULL with zero, but missing and zero are different business states. Use a fallback only when that interpretation is correct.
Rounding Calculated Values
ROUND can wrap arithmetic expressions such as quantity multiplied by unit price.
SELECT order_item_id,
ROUND(quantity * unit_price, 2) AS line_total
FROM order_items;
Define the precision of quantity, price, and intermediate multiplication. A database may widen or reduce numeric types according to product-specific rules, and a final cast can overflow if the chosen target is too narrow.
Percentages and Integer Division
Percentage calculations should avoid integer division and zero denominators.
SELECT ROUND(
completed_tasks * 100.0 /
NULLIF(total_tasks, 0),
2
) AS completion_percentage
FROM projects;
The decimal literal encourages decimal arithmetic in many systems, while NULLIF prevents division by zero by returning NULL. Exact expression typing remains DBMS-specific.
Using ROUND with AVG
Applying ROUND after AVG preserves the aggregate's available precision until the final result.
SELECT department,
ROUND(AVG(salary), 2) AS average_salary
FROM employees
GROUP BY department;
This is usually preferable to rounding every salary before averaging unless the source values themselves must first be rounded under a business rule.
Using ROUND with SUM
A final total can be rounded after all rows are added.
SELECT ROUND(SUM(amount), 2) AS total_amount
FROM payments;
If the amount column already has scale two, the sum may already be exact at that scale. ROUND still communicates the required output precision but does not necessarily control visual trailing zeros.
ROUND SUM Versus SUM ROUND
The order of operations can change the answer.
-- Add precise values, then round once
SELECT ROUND(SUM(amount), 2) AS rounded_total
FROM invoice_item;
-- Round every value, then add
SELECT SUM(ROUND(amount, 2)) AS sum_of_rounded_items
FROM invoice_item;
The first method retains row precision until the final total. The second enforces line-level rounding. Accounting, tax, payroll, and billing rules must specify which method applies.
Tax per Item Versus Tax on Total
Invoice tax can differ by a few cents depending on sequencing. One policy calculates and rounds tax for each line, then sums the rounded amounts. Another sums taxable values, calculates tax once, and rounds the final tax.
Neither method is universally correct. Jurisdiction, invoice rules, currency minor units, and contractual requirements determine the answer. Store enough detail to reproduce the chosen method and explain reconciliation differences.
Discount Calculations
A percentage discount can produce more decimals than the stored price supports.
SELECT price,
discount_rate,
ROUND(price * discount_rate, 2) AS discount_amount,
ROUND(price - (price * discount_rate), 2) AS final_price
FROM product_offer;
Confirm whether the discount amount is rounded before subtraction or whether only the final price is rounded. Parentheses make the intended operation order visible.
Currency Conversion
Currency conversion combines source amount, rate, timestamp, currency pair, and rounding policy. Rates often require substantially more scale than final monetary amounts.
SELECT source_amount,
exchange_rate,
ROUND(source_amount * exchange_rate, target_minor_units)
AS target_amount
FROM currency_conversion;
Different currencies use different minor units. Preserve the rate and unrounded calculation used for audit, and do not assume that every currency rounds to two decimals.
Interest and Accrual Calculations
Interest systems must define compounding frequency, day-count convention, calculation scale, posting frequency, and rounding stage. Rounding each daily accrual can differ from accumulating high-precision interest and rounding at posting time.
Use exact numeric types sized for intermediate precision and retain audit inputs. A generic ROUND call cannot decide the financial policy.
Measurements and Scientific Data
Measurements should be rounded according to instrument precision, uncertainty, and reporting standards. Displaying many decimal places does not create accuracy that the source did not measure.
SELECT sensor_id,
measured_at,
ROUND(temperature_celsius, 1) AS temperature_display
FROM temperature_reading;
Preserve raw readings when later calibration, aggregation, or scientific analysis requires them. Use rounded output for a declared reporting purpose.
Decimal Places Versus Significant Figures
ROUND normally targets a fixed decimal position, not a fixed number of significant figures. Rounding 0.0012345 to two decimal places produces zero, while rounding it to two significant figures should produce 0.0012. Large values create the opposite issue: 123456 rounded to two decimal places is unchanged, but two significant figures would be approximately 120000.
Significant-figure rounding usually requires determining the value's order of magnitude, calculating a dynamic precision, and handling zero, negatives, and extreme ranges. That logic can involve logarithms and powers and is highly sensitive to approximate numeric types.
-- Conceptual only: significant-figure logic is DBMS-specific
dynamic_precision = significant_digits
- 1
- FLOOR(LOG10(ABS(value)))
Use a tested domain function or analytical library when significant figures are a genuine requirement. Document whether trailing zeros communicate measured precision, because numeric equality alone cannot preserve that display meaning.
ROUND Versus Truncation
Rounding chooses a nearby value according to following digits. Truncation discards unwanted digits without normal nearest-value rounding.
| Expression concept | Result for 12.789 at two places |
|---|---|
| ROUND | 12.79 |
| TRUNCATE / TRUNC | 12.78 |
Function names and signatures vary. SQL Server also has a ROUND third argument that requests truncation behavior.
ROUND Versus FLOOR and CEILING
FLOOR returns the greatest integer less than or equal to the input. CEILING returns the smallest integer greater than or equal to it. They are directional boundary functions, not nearest-value rounding.
| Function | 12.3 | 12.7 | -12.3 |
|---|---|---|---|
| ROUND | 12 | 13 | -12 |
| FLOOR | 12 | 12 | -13 |
| CEILING | 13 | 13 | -12 |
The negative example shows why FLOOR cannot be described as merely removing decimals.
ROUND Versus CAST
ROUND performs a numeric rounding operation. CAST changes data type. Casting to a fixed-scale decimal may itself round or reject a value according to DBMS rules, but the two operations communicate different intent.
SELECT ROUND(measurement, 2) AS rounded_value,
CAST(measurement AS DECIMAL(12, 2)) AS converted_value
FROM measurement_log;
Use ROUND when the business rule is rounding. Use CAST when the requirement is type conversion, and test conversion overflow and tie behavior.
Rounding in WHERE Predicates
A functional predicate can reduce the usefulness of a conventional index.
SELECT product_id, price
FROM products
WHERE ROUND(price, 0) = 100;
A range can sometimes express the same intent more efficiently, such as values from 99.5 through the correct upper boundary. Exact boundaries depend on tie policy, data type, and DBMS behavior. Verify equivalence before rewriting.
Rounding in ORDER BY
Ordering by a rounded value groups numerically different values under the same sort key.
SELECT product_name, price
FROM products
ORDER BY ROUND(price, 0), price, product_name;
Adding the original value and a stable identifier as tie-breakers produces deterministic ordering. Decide whether the business wants rounded bands or exact numeric order.
Rounding in GROUP BY
Grouping by ROUND creates numeric bands at the selected precision.
SELECT ROUND(measurement_value, 1) AS measurement_band,
COUNT(*) AS reading_count
FROM sensor_reading
GROUP BY ROUND(measurement_value, 1)
ORDER BY measurement_band;
This can support exploratory distributions, but boundary values depend on the rounding rule. Store explicit ranges when business categories require controlled inclusive and exclusive boundaries.
MySQL ROUND Behavior
MySQL supports positive, zero, and negative precision. Its documentation distinguishes exact-value and approximate-value rounding behavior, so DECIMAL input and floating-point input can differ at ties.
SELECT ROUND(123.4567, 2),
ROUND(1234, -2);
Test the exact column type rather than only a literal, because literal classification and expression promotion can affect the path used.
SQL Server ROUND Behavior
SQL Server supports a third argument. Zero or omitted means normal rounding; a nonzero value requests truncation.
SELECT ROUND(123.4567, 2) AS rounded_value,
ROUND(123.4567, 2, 1) AS truncated_value;
-- Common results: 123.46 and 123.45
Negative precision can overflow when the result needs more integer digits than the input decimal type allows. Cast to an adequately sized exact type when necessary.
PostgreSQL ROUND Behavior
PostgreSQL has different ROUND signatures for numeric and floating-point values. Numeric values and floating-point values can use different tie behavior, and floating-point behavior can depend on the platform library.
SELECT ROUND(123.4567::numeric, 2) AS rounded_numeric;
For deterministic decimal policy, use NUMERIC with declared precision and test the actual expression types produced by arithmetic and aggregates.
Oracle ROUND Behavior
Oracle supports decimal and negative precision for numeric values.
SELECT ROUND(123.4567, 2) AS rounded_decimal,
ROUND(1234, -2) AS rounded_hundred
FROM dual;
Oracle also uses ROUND for dates with format models, which is a separate feature with different semantics. Keep numeric and date rounding requirements clearly separated.
Cross-Database Portability
The common two-argument syntax looks portable, but differences remain in return type, tie-breaking, floating-point behavior, overflow, negative precision, optional arguments, and implicit conversion. A query returning the same sample value is not sufficient proof of equivalent behavior.
Create a shared test matrix with positive and negative ties, exact and approximate inputs, large values, NULL, negative precision, and overflow boundaries for every supported engine.
Expression Type Propagation
The type passed to ROUND is not always the type declared on a single source column. Arithmetic operators, literals, CASE branches, COALESCE, aggregate functions, and implicit conversions can change precision, scale, or approximate-versus-exact behavior before ROUND runs. For example, multiplying two decimals can produce a wider intermediate type, while mixing a decimal with a floating-point value can move the expression into approximate arithmetic.
SELECT SQL_VARIANT_PROPERTY(
CAST(quantity AS DECIMAL(12, 3))
* CAST(unit_price AS DECIMAL(19, 6)),
'BaseType'
) AS expression_type
FROM order_items;
The inspection syntax above is SQL Server-specific, but every platform offers catalog or expression tools that can help. Review the effective type of the complete expression, not just its inputs. Explicit casts at controlled boundaries make intent clearer, but they must allow enough precision for intermediate results.
Overflow and Negative Precision
Rounding left of the decimal point can require an additional integer digit. A decimal value that fits before rounding may overflow afterward. For example, rounding 748.58 to the nearest thousand produces 1000, which cannot fit in a decimal type that allows only three integer digits.
-- Widen before negative-precision rounding when required
SELECT ROUND(CAST(748.58 AS DECIMAL(8, 2)), -3)
AS rounded_thousand;
Do not solve overflow by converting everything to floating point because that sacrifices exact decimal semantics. Size the exact type for the largest possible rounded result, test maximum positive and negative values, and confirm how the target DBMS reports arithmetic overflow.
Worked Invoice Example
Consider three invoice lines whose unrounded extended amounts are 19.995, 19.995, and 5.005. Rounding each line to two places under a half-away-from-zero policy gives 20.00, 20.00, and 5.01, for a total of 45.01. Summing first gives 44.995, which may round to 45.00 under the same policy. The one-cent difference is caused by stage, not a SQL error.
SELECT SUM(ROUND(quantity * unit_price, 2))
AS total_from_rounded_lines,
ROUND(SUM(quantity * unit_price), 2)
AS rounded_document_total
FROM invoice_item
WHERE invoice_id = :invoice_id;
The invoice policy must decide which value is authoritative. Printed lines, tax calculations, ledger postings, payment requests, and refunds should all reconcile to that same decision. Store a documented adjustment when legal rules require line totals and document totals to balance.
Refunds, Credits, and Reversals
Negative financial transactions expose inconsistencies that positive-only tests miss. A refund should usually reverse the original posted amount, not recalculate today's value using a changed rate or policy. If an original line tax was rounded to 1.23, reversing that line generally requires -1.23 even if a fresh calculation from retained inputs yields a slightly different intermediate result.
Test negative quantities, credit notes, partial refunds, canceled lines, and chargebacks. Define whether rounding adjustments are reversed proportionally or assigned to a final line. Preserve links to original postings so reconciliation can explain every cent.
Regulatory and Contractual Requirements
Tax agencies, accounting standards, payroll rules, card networks, utility tariffs, and customer contracts may prescribe rounding precision and sequence. Requirements can differ by jurisdiction, transaction type, currency, and effective date. A globally reused helper called round_money is unsafe if it hides those distinctions.
Represent the policy as versioned configuration or clearly owned code, record the version used for each posted transaction, and have responsible business experts approve changes. Tests should include published examples from the governing source when available. A software upgrade must not silently change historical calculations because a database's floating-point or formatting behavior changed.
Data Exchange and Serialization
APIs and files may serialize exact decimals as JSON numbers or strings. A receiving language can parse a JSON number into binary floating point and lose the exact value that SQL produced. Financial contracts often use strings with documented scale or decimal-aware schemas to preserve meaning.
Define whether trailing zeros are significant, which decimal separator is used, whether scientific notation is allowed, and which layer performs final rounding. Validate round-trip behavior from database to API, client, message queue, warehouse, and back. Numeric equality and textual equality are not the same contract.
Optimizing Repeated Rounded Searches
If users frequently filter by a stable rounded bucket, a computed or generated column can store the bucket expression and support an index. This can be useful for reporting bands, but it couples the schema to one precision and rule.
-- Conceptual syntax; exact support varies
ALTER TABLE sensor_reading
ADD value_tenth DECIMAL(12, 1)
GENERATED ALWAYS AS (ROUND(measurement_value, 1));
CREATE INDEX ix_sensor_reading_value_tenth
ON sensor_reading (value_tenth);
Prefer explicit range predicates when users search arbitrary precision. Review whether rounded buckets create skewed distributions, and keep the raw value for exact analysis. Any generated expression must match the production query's data type and semantics.
Monitoring Rounding Quality
Operational controls can track total rounding adjustments, maximum adjustment per transaction, count of halfway cases, reconciliation differences, overflow errors, and mismatches between database and application calculations. Break metrics down by policy version, currency, jurisdiction, and source service.
SELECT currency_code,
COUNT(*) AS payment_count,
SUM(posted_amount - calculated_amount) AS total_adjustment,
MAX(ABS(posted_amount - calculated_amount)) AS max_adjustment
FROM payment_audit
GROUP BY currency_code;
Small individual differences can become material at high volume. Alerts should use business-approved thresholds and preserve transaction-level evidence for investigation rather than only aggregate totals.
Defining a Financial Rounding Policy
A complete policy states the source type, calculation scale, target scale, tie rule, currency minor units, line-level or document-level stage, treatment of negative adjustments, and reconciliation method. It also identifies which result is stored and which intermediate values are retained.
Policy ownership belongs to finance, accounting, tax, or another responsible business authority. Developers implement and test the rule; they should not infer it from a screen mockup showing two decimals.
Audit and Reconciliation
Financial records should retain enough information to reproduce a rounded result: source amount, rates, quantities, rule version, calculation timestamp, and sequence. If only the rounded total remains, later reconciliation may be impossible.
Reports should explain differences caused by line versus total rounding rather than forcing totals to agree through undocumented adjustments. When a balancing adjustment is required, store it explicitly with a reason and audit trail.
Storing Raw and Rounded Values
Preserve raw values when future recalculation, analysis, calibration, or policy changes may need them. Store a rounded value only when it represents a posted, legally fixed, or contractually authoritative amount.
A schema can keep both, but names should clarify meaning, such as calculated_tax_unrounded and posted_tax_amount. Avoid maintaining two columns whose relationship is undocumented or inconsistently updated.
Safe Rounding Migrations
Before reducing stored scale, preview old and proposed values, count changed rows, calculate total differences, inspect maximum adjustments, and verify constraints and downstream contracts.
SELECT amount AS old_amount,
ROUND(amount, 2) AS proposed_amount,
amount - ROUND(amount, 2) AS rounding_difference
FROM payment_staging
WHERE amount <> ROUND(amount, 2);
Use controlled batches, transactions appropriate to table size, backups, and a rollback plan. Do not overwrite audit-required precision without approval.
Testing Strategy
Test values just below, exactly at, and just above every boundary. Include positive and negative ties, zero, NULL, maximum magnitude, negative precision, exact decimal, floating point, aggregate order, division by zero, and overflow. Use values such as 1.2449, 1.2450, 1.2451, -1.2450, 99.5, and 100.5 where appropriate.
Run tests on every supported DBMS and numeric type. Assert both value and data type. Financial integration tests should reconcile line items, tax, discounts, document totals, refunds, and exported reports under the same policy version.
Common Mistakes
Frequent errors include using FLOAT for exact money, assuming ROUND displays trailing zeros, rounding every intermediate value without a rule, confusing ROUND with FLOOR or truncation, and treating every currency as two decimals. Other mistakes include rounding in predicates without reviewing indexes and assuming all databases resolve ties identically.
Developers also overlook ROUND(SUM(value)) versus SUM(ROUND(value)), negative values, type conversion overflow, and the audit impact of discarding original precision.
Best-Practice Checklist
- Define precision, stage, tie rule, and ownership before coding.
- Use DECIMAL or NUMERIC for exact decimal requirements.
- Keep enough intermediate precision for calculation.
- Distinguish numeric rounding from visual formatting.
- Test positive and negative halfway values explicitly.
- Document line-level versus total-level rounding.
- Handle zero denominators with a deliberate policy.
- Review expression types and overflow boundaries.
- Measure functional predicates before relying on indexes.
- Preserve raw or audit inputs when recalculation is required.
- Verify behavior on every supported database engine.
- Version financial rules and reconcile outputs end to end.
Interview Questions and Answers
What does ROUND do? It rounds a numeric expression to a requested precision according to the database's rules.
What does a precision of two mean? It normally retains two digits to the right of the decimal point.
What does zero mean? It rounds at whole-number precision.
What does negative precision mean? It rounds positions left of the decimal, such as tens or hundreds, where supported.
Does ROUND format trailing zeros? Not necessarily. Formatting controls visual representation.
What is ROUND versus FLOOR? ROUND chooses a nearest value; FLOOR moves to the greatest integer not above the input.
What is ROUND versus CEILING? CEILING moves to the smallest integer not below the input.
Why can FLOAT appear surprising? It stores a binary approximation that may not equal the typed decimal exactly.
Can ROUND affect index use? Yes, when applied to an indexed column in a predicate.
Why can ROUND(SUM()) differ from SUM(ROUND())? One rounds once after aggregation; the other rounds each row before aggregation.
Key Takeaway
ROUND is a numeric operation, not a complete display or financial policy. Its correctness depends on data type, precision, tie behavior, calculation stage, and database implementation.
RELIABLE ROUNDING = EXACT NUMERIC TYPES WHEN REQUIRED
+ EXPLICIT PRECISION AND TIE POLICY
+ CORRECT CALCULATION STAGE
+ NEGATIVE AND BOUNDARY TESTING
+ DBMS-SPECIFIC VERIFICATION
+ AUDITABLE BUSINESS RULES
Use ROUND where the business rule requires a numeric result at a defined precision. Keep raw precision when future calculation or audit needs it, format only at the presentation boundary, and test line-level and aggregate behavior across every supported database.