ROUND Function

Rounds the first argument to the nearest multiple of the second argument, or to the nearest integer when the second argument is omitted.

Category: Truncation
Returned data type: DOUBLE

Syntax

Arguments

expression

specifies any valid expression that evaluates to a numeric value, to be rounded.

Data type DOUBLE
See <sql-expression>
FedSQL Expressions

rounding-unit

specifies a positive numeric expression that specifies the rounding unit.

Data type DOUBLE
See <sql-expression>
FedSQL Expressions

Details

Basic Concepts

The ROUND function rounds the first argument to a value that is very close to a multiple of the second argument. The results might not be an exact multiple of the second argument.

Differences between Binary and Decimal Arithmetic

Computers use binary arithmetic with finite precision. If you work with numbers that do not have an exact binary representation, computers often produce results that differ slightly from the results that are produced with decimal arithmetic.
For example, the decimal values 0.1 and 0.3 do not have exact binary representations. In decimal arithmetic, 3*0.1 is exactly equal to 0.3, but this equality is not true in binary arithmetic.

The Effects of Rounding

Rounding by definition finds an exact multiple of the rounding unit that is closest to the value to be rounded. For example, 0.33 rounded to the nearest tenth equals 3*0.1 or 0.3 in decimal arithmetic. In binary arithmetic, 0.33 rounded to the nearest tenth equals 3*0.1, and not 0.3, because 0.3 is not an exact multiple of one tenth in binary arithmetic.
The ROUND function returns the value that is based on decimal arithmetic, even though this value is sometimes not the exact, mathematically correct result. In the example ROUND(0.33,0.1), ROUND returns 0.3 and not 3*0.1.

Testing for Approximate Equality

You should not use the ROUND function as a general method to test for approximate equality. Two numbers that differ only in the least significant bit can round to different values if one number rounds down and the other number rounds up. Testing for approximate equality depends on how the numbers have been computed. If both numbers are computed to high relative precision, you could test for approximate equality by using the ABS and the MAX functions.

Producing Expected Results

In general, ROUND(expression, rounding-unit) produces the result that you expect from decimal arithmetic if the result has no more than nine significant digits and any of the following conditions are true:
  • The rounding unit is an integer.
  • The rounding unit is a power of 10 greater than or equal to 1e-15. (footnote 1)
  • The result that you expect from decimal arithmetic has no more than four decimal places.
For example:
select round(1234.56789,100);            Result: 1200
select round(1234.56789,10);             Result: 1230
select round(1234.56789,1);              Result: 1235 
select round(1234.56789,.1);             Result: 1234.6
select round(1234.56789,.01);            Result: 1234.57
select round(1234.56789,.001);           Result: 1234.568
select round(1234.56789,.0001);          Result: 1234.5679
select round(1234.56789,.00001);         Result: 1234.56789
select round(1234.56789,.1111);          Result: 1234.5432
select round(1234.56789,.11111);         Result: 1234.54321

When the Rounding Unit Is the Reciprocal of an Integer

When the rounding unit is the reciprocal of an integer (footnote 2) , the ROUND function computes the result by dividing by the integer. Therefore, you can safely compare the result from ROUND with the ratio of two integers, but not with a multiple of the rounding unit.

Computing Results in Special Cases

The ROUND function computes the result by multiplying an integer by the rounding unit when all of the following conditions are true:
  • The rounding unit is not an integer.
  • The rounding unit is not a power of 10.
  • The rounding unit is not the reciprocal of an integer.
  • The result that you expect from decimal arithmetic has no more than four decimal places.
For example:
select round(1234.56789,.11111) - 11111*.11111;
Returns the value 0 (zero).

Computing Results When the Value Is Halfway between Multiples of the Rounding Unit

When the value to be rounded is approximately halfway between two multiples of the rounding unit, the ROUND function rounds up the absolute value and restores the original sign.

Example

The following statement illustrates the ROUND function:
Statement
Result
select round(9.5,10);
10
FOOTNOTE 1:If the rounding unit is less than one, ROUND treats it as a power of 10 if the reciprocal of the rounding unit differs from a power of 10 in at most the three or four least significant bits.[return]
FOOTNOTE 2:ROUND treats the rounding unit as a reciprocal of an integer if the reciprocal of the rounding unit differs from an integer in at most the three or four least significant bits.[return]
Last updated: February 23, 2017