![]() Previous |
![]() Next |
When a number is specified as an argument, the ROUND function returns the number rounded to the nearest multiple of a second number you specify or to the number of decimal places indicated by the second number.
Return Value
DECIMAL (when the round type is MULTIPLE)
NUMBER (when the round type is DECIMAL)
Syntax
ROUND(number_exp roundvalue) [MULTIPLE|DECIMAL]
Parameters
An expression that identifies the number to round.
A value that specifies the basis for rounding.
When the round type is MULTIPLE:
number_exp is rounded to the nearest multiple of roundvalue.
roundvalue can be an INTEGER
or decimal number.
When the round type is DECIMAL:
roundvalue specifies the number of places to the right or left of the decimal point to which number_exp should be rounded. When roundvalue is positive, digits to the right of the decimal point are rounded. When it is negative, digits to the left of the decimal point are rounded.
When roundvalue is omitted, number_exp is rounded to 0
decimal places.
roundvalue must be an INTEGER
.
(Default) Specifies that rounding is performed by rounding to the nearest multiple of roundvalue.
Specifies that rounding is performed by rounding to the number of decimal places indicated by roundvalue.
Usage Notes
Using ROUND to Compare Expressions
A DECIMAL value might be stored in a slightly different form than shows up at the level of significant digits you are using. This small difference can cause unexpected results when you are comparing two expressions. The problem can occur even when you are comparing INTEGER expressions that involve calculations because many calculations are done only after converting INTEGER values to DECIMAL values. You do not generally see the difference in reports because reports usually show only two or three decimal places.
For example, when you compare two numbers with the EQ or NE operators, you probably want to ignore any difference caused by the least significant digits. When expense
was stored as 100.00000001
, the least significant digit would not be ignored by the simple form of the comparison.
The statement
SHOW expense EQ 100.00
produces the following result.
NO
However, you can use ROUND to force EQ or NE to ignore the least significant digits.
SHOW ROUND(expense, .01) EQ 100.00
This statement produces the following result.
YES
Using ABS to Compare Expressions
When speed of calculation is important in your application, you may want to use the ABS function with LT to compare numbers, instead of using ROUND with EQ or NE.
Examples
Rounding to Different Multiples
The following statements show the results of rounding the expression 2/3
to different multiples. The value of the DECIMALS setting is 2
.
The statement
SHOW ROUND(2/3, .01)
produces the following result.
0.67
The statement
SHOW ROUND(2/3, .1)
produces the following result.
0.70
The statement
SHOW ROUND(2/3, .5)
produces the following result.
0.50
Rounding to the Nearest Thousand
The following example shows sales
rounded to the nearest thousand.
LIMIT month TO FIRST 4 LIMIT district TO FIRST 1 REPORT ROUND(sales 1000)
These statements produce the following output.
DISTRICT: BOSTON -------------ROUND(SALES 1000)------------- -------------------MONTH------------------- PRODUCT Jan95 Feb95 Mar95 Apr95 -------------- ---------- ---------- ---------- ---------- Tents 32,000.00 33,000.00 43,000.00 58,000.00 Canoes 66,000.00 76,000.00 92,000.00 126,000.00 Racquets 52,000.00 57,000.00 59,000.00 69,000.00 Sportswear 53,000.00 59,000.00 63,000.00 68,000.00 Footwear 91,000.00 87,000.00 100,000.00 108,000.00
Rounding to the Nearest Multiple of 12
To show units
rounded to the nearest multiple of 12, use the following statements.
LIMIT month TO FIRST 4 LIMIT district TO FIRST 1 REPORT DECIMAL 0 ROUND(units 12)
These statements produce the following output.
DISTRICT: BOSTON --------------ROUND(UNITS 12)-------------- -------------------MONTH------------------- PRODUCT Jan95 Feb95 Mar95 Apr95 -------------- ---------- ---------- ---------- ---------- Tents 204 204 264 360 Canoes 348 396 480 660 Racquets 996 1,080 1,116 1,308 Sportswear 1,092 1,212 1,296 1,404 Footwear 2,532 2,400 2,772 2,976