The ROUND numeric function rounds a supplied value to a given number of places.
Syntax
>>-ROUND--(--source_number--,--precision------------------------> >--+----------------------------+--)--------------------------->< | (1) | '-MODE------| RoundingMode |-' RoundingMode |--+-ROUND_UP--------+------------------------------------------| +-ROUND_DOWN------+ +-ROUND_CEILING---+ +-ROUND_FLOOR-----+ +-ROUND_HALF_UP---+ +-ROUND_HALF_EVEN-+ '-ROUND_HALF_DOWN-'
Notes:
- If you do not specify MODE, a value of ROUND_HALF_EVEN is used.
If precision is a positive number, source_number is rounded to precision places right of the decimal point. If precision is negative, the result is source_number rounded to the absolute value of precision places to the left of the decimal point.
source_number can be any built-in numeric data type; precision must be an integer. The result is of the same data type as the source_number parameter unless source_number is NULL, in which case the result is NULL.
This means that the result
of the function is:
- INTEGER if source_number is INTEGER
- FLOAT if source_number is FLOAT
- DECIMAL if source_number is DECIMAL
RoundingMode
RoundingMode can
take one of the following values:
- ROUND_UP
- Round away from zero. Always increments the digit prior to a nonzero discarded fraction. This rounding mode never decreases the magnitude of the calculated value.
- ROUND_DOWN
- Round towards zero. Never increments the digit prior to a discarded fraction, that is, truncates. This rounding mode never increases the magnitude of the calculated value.
- ROUND_CEILING
- Round towards positive infinity. If the decimal is positive, behaves as for ROUND_UP; if negative, behaves as for ROUND_DOWN. This rounding mode never decreases the calculated value.
- ROUND_FLOOR
- Round towards negative infinity. If the decimal is positive, behaves as for ROUND_DOWN; if negative, behaves as for ROUND_UP. This rounding mode never increases the calculated value.
- ROUND_HALF_UP
- Round towards "nearest neighbor" unless both neighbors are equidistant, in which case round up. Behaves as for ROUND_UP if the discarded fraction is greater than, or equal to, 0.5; otherwise, behaves as for ROUND_DOWN. This is the rounding mode that is typically taught in schools.
- ROUND_HALF_DOWN
- Round towards "nearest neighbor" unless both neighbors are equidistant, in which case round down. Behaves as for ROUND_UP if the discarded fraction is grater than 0.5; otherwise, behaves as for ROUND_DOWN.
- ROUND_HALF_EVEN
- Round towards the "nearest neighbor" unless both neighbors are equidistant, in which case, round towards the even neighbor. Behaves as for ROUND_HALF_UP if the digit to the left of the discarded fraction is odd; behaves as for ROUND_HALF_DOWN if it is even. This is the rounding mode that minimizes cumulative error when applied repeatedly over a sequence of calculations, and is sometimes referred to as Banker's rounding.
The following table gives a summary of rounding
operations, with a precision of zero, under different rounding modes.
| Input number | ROUND UP | ROUND DOWN | ROUND CEILING | ROUND FLOOR | ROUND HALF UP | ROUND HALF DOWN | ROUND HALF EVEN |
|---|---|---|---|---|---|---|---|
| 5.5 | 6 | 5 | 6 | 5 | 6 | 5 | 6 |
| 2.5 | 3 | 2 | 3 | 2 | 3 | 2 | 2 |
| 1.6 | 2 | 1 | 2 | 1 | 2 | 2 | 2 |
| 1.1 | 2 | 1 | 2 | 1 | 1 | 1 | 1 |
| 1.0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| -1.0 | -1 | -1 | -1 | -1 | -1 | -1 | -1 |
| -1.1 | -2 | -1 | -1 | -2 | -1 | -1 | -1 |
| -1.6 | -2 | -1 | -1 | -2 | -2 | -2 | -2 |
| -2.5 | -3 | -2 | -2 | -3 | -3 | -2 | -2 |
| -5.5 | -6 | -5 | -5 | -6 | -6 | -5 | -6 |
Examples using the default
rounding mode (ROUND_HALF_EVEN):
ROUND(27.75, 2)ROUND(27.75, 1)ROUND(27.75, 0)ROUND(27.75, -1)Examples using a rounding
mode with a precision of zero:
ROUND(5.5, 0 MODE ROUND_UP);ROUND(5.5, 0 MODE ROUND_DOWN);ROUND(5.5, 0 MODE ROUND_CEILING);ROUND(5.5, 0 MODE ROUND_FLOOR);ROUND(5.5, 0 MODE ROUND_HALF_UP);ROUND(5.5, 0 MODE ROUND_HALF_DOWN);ROUND(5.5, 0 MODE ROUND_HALF_EVEN);ROUND(2.5, 0 MODE ROUND_UP);ROUND(2.5, 0 MODE ROUND_DOWN);ROUND(2.5, 0 MODE ROUND_CEILING);ROUND(2.5, 0 MODE ROUND_FLOOR);ROUND(2.5, 0 MODE ROUND_HALF_UP);ROUND(2.5, 0 MODE ROUND_HALF_DOWN);ROUND(2.5, 0 MODE ROUND_HALF_EVEN);If possible, the scale is changed to the given value. If the result cannot be represented within the given scale, it is INFINITY.
Last updated Friday, 21 July 2017