ROUND function

The ROUND numeric function rounds a supplied value to a given number of places.

Syntax

Read syntax diagramSkip visual syntax diagramROUND(source_number, precisionMODE1RoundingMode)
RoundingMode
Read syntax diagramSkip visual syntax diagramROUND_UPROUND_DOWNROUND_CEILINGROUND_FLOORROUND_HALF_UPROUND_HALF_EVENROUND_HALF_DOWN
Notes:
  • 1 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
When rounding, the banker's or half-even symmetric rounding rules are used by default, unless a RoundingMode is specified.

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)
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.