ROUND function
The ROUND numeric function rounds a supplied value to a given number of places.
Syntax
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:
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)
returns 27.75
ROUND(27.75, 1)
returns 27.8
ROUND(27.75, 0)
returns 28
ROUND(27.75, -1)
returns 30
Examples using a rounding
mode with a precision of zero:
ROUND(5.5, 0 MODE ROUND_UP);
returns 6
ROUND(5.5, 0 MODE ROUND_DOWN);
returns 5
ROUND(5.5, 0 MODE ROUND_CEILING);
returns 6
ROUND(5.5, 0 MODE ROUND_FLOOR);
returns 5
ROUND(5.5, 0 MODE ROUND_HALF_UP);
returns 6
ROUND(5.5, 0 MODE ROUND_HALF_DOWN);
returns 5
ROUND(5.5, 0 MODE ROUND_HALF_EVEN);
returns 6
ROUND(2.5, 0 MODE ROUND_UP);
returns 3
ROUND(2.5, 0 MODE ROUND_DOWN);
returns 2
ROUND(2.5, 0 MODE ROUND_CEILING);
returns 3
ROUND(2.5, 0 MODE ROUND_FLOOR);
returns 2
ROUND(2.5, 0 MODE ROUND_HALF_UP);
returns 3
ROUND(2.5, 0 MODE ROUND_HALF_DOWN);
returns 2
ROUND(2.5, 0 MODE ROUND_HALF_EVEN);
returns 2
If possible, the scale is changed to the given value. If the result cannot be represented within the given scale, it is INFINITY.