Categories:

Numeric Functions (Rounding and Truncation)

# ROUND¶

Returns rounded values for `input_expr`.

## Syntax¶

```ROUND( <input_expr> [ , <scale_expr> [ , <rounding_mode> ] ] )
```

## Arguments¶

Required:

`input_expr`

The value or expression to operate on. The data type should be one of the numeric data types, such as FLOAT or NUMBER.

Optional:

`scale_expr`

The number of digits the output should include after the decimal point. The expression should evaluate to an integer from -38 to +38.

The default `scale_expr` is zero, meaning that the function removes all digits after the decimal point.

For information about negative numbers, see the Usage Notes below.

`rounding_mode`

The rounding mode to use. You can specify one of the following values:

• `'HALF_AWAY_FROM_ZERO'`. This mode rounds the value half away from zero.

• `'HALF_TO_EVEN'`. This mode rounds the value half to even.

Default: `'HALF_AWAY_FROM_ZERO'`

Note

If you specify either value for the `rounding_mode` argument, the data type of `input_expr` must be one of the data types for a fixed-point number.

Data types for floating point numbers (e.g. FLOAT) are not supported with this argument.

## Returns¶

The return type is based on the input type:

• If the input is FLOAT, then the data type of the returned value is FLOAT.

• If the input is NUMBER, then the data type of the returned value is NUMBER.

If the input scale is greater than or equal to zero, then the output scale generally matches the input scale.

If the input scale is negative, then the output scale is 0.

For example:

• The data type returned by ROUND(3.14::NUMBER(4, 1), 1) is NUMBER(4, 1).

• The data type returned by ROUND(3.14::NUMBER(4, 0), 0) is NUMBER(4, 0).

If the scale is zero, then the value is effectively an integer.

## Usage Notes¶

• If `scale_expr` is negative, then it specifies the number of places before the decimal point to which to adjust the number. For example, if the scale is -2, then the result is a multiple of 100.

• If `scale_expr` is larger than the input expression scale, the function does not have any effect.

• If either the `input_expr` or the `scale_expr` is NULL, then the result is NULL.

• By default, half-points are rounded away from zero for decimals. For example, -0.5 is rounded to -1.0.

To change the rounding mode to round the value half to even (e.g. to round -0.5 to 0), pass in `'HALF_TO_EVEN'` for the `rounding_mode` argument.

Note

If you specify the `rounding_mode` argument, the data type of the `input_expr` argument must be one of the data types for a fixed-point number.

• Floating point numbers are approximate values. A floating point number might not round as expected.

• If rounding brings the number outside of the range of values of the data type, then an error is returned.

## Examples¶

This first example shows a simple example of `ROUND`, with the default number of decimal places (0):

```SELECT ROUND(135.135), ROUND(-975.975);
+----------------+-----------------+
| ROUND(135.135) | ROUND(-975.975) |
|----------------+-----------------|
|            135 |            -976 |
+----------------+-----------------+
```

The next example uses a range of values for the scale parameter:

```SELECT n, scale, ROUND(n, scale)
FROM test_ceiling
ORDER BY n, scale;
+----------+-------+-----------------+
|        N | SCALE | ROUND(N, SCALE) |
|----------+-------+-----------------|
| -975.975 |    -1 |        -980     |
| -975.975 |     0 |        -976     |
| -975.975 |     2 |        -975.98  |
|  135.135 |    -2 |         100     |
|  135.135 |     0 |         135     |
|  135.135 |     1 |         135.1   |
|  135.135 |     3 |         135.135 |
|  135.135 |    50 |         135.135 |
|  135.135 |  NULL |            NULL |
+----------+-------+-----------------+
```

The next example shows the difference between using the default rounding mode (`'HALF_AWAY_FROM_ZERO'`) and the rounding mode `'HALF_TO_EVEN'`:

```SELECT ROUND(2.5, 0), ROUND(2.5, 0, 'HALF_TO_EVEN');

+---------------+-------------------------------+
| ROUND(2.5, 0) | ROUND(2.5, 0, 'HALF_TO_EVEN') |
|---------------+-------------------------------|
|             3 |                             2 |
+---------------+-------------------------------+

SELECT ROUND(-2.5, 0), ROUND(-2.5, 0, 'HALF_TO_EVEN');

+----------------+--------------------------------+
| ROUND(-2.5, 0) | ROUND(-2.5, 0, 'HALF_TO_EVEN') |
|----------------+--------------------------------|
|             -3 |                             -2 |
+----------------+--------------------------------+
```

The next example shows that FLOAT values are not always stored exactly. As you can see below, in some cases .005 is rounded to .01, while in other cases it is rounded to 0. The difference is not in the rounding; the difference is actually in the underlying representation of the floating point number; 1.005 is stored as a number very slightly smaller than 1.005 (approximately 1.004999). The DECIMAL value, however is stored as an exact number, and is rounded to .01 as expected in all cases.

Create and load a table:

```CREATE OR REPLACE TEMP TABLE rnd1(f float, d DECIMAL(10, 3));
INSERT INTO rnd1 (f, d) VALUES
( -10.005,  -10.005),
(  -1.005,   -1.005),
(   1.005,    1.005),
(  10.005,   10.005)
;
```

Show examples of difference between rounded FLOAT values and rounded DECIMAL values:

```select f, round(f, 2),
d, round(d, 2)
from rnd1
order by 1;
+---------+-------------+---------+-------------+
|       F | ROUND(F, 2) |       D | ROUND(D, 2) |
|---------+-------------+---------+-------------|
| -10.005 |      -10.01 | -10.005 |      -10.01 |
|  -1.005 |       -1    |  -1.005 |       -1.01 |
|   1.005 |        1    |   1.005 |        1.01 |
|  10.005 |       10.01 |  10.005 |       10.01 |
+---------+-------------+---------+-------------+
```
Language: English