Product Documentation

Appearance

All platforms

ROUND ​

Round a number (up or down) to the nearest integer. Optionally, specify the number of decimal places for the rounding.

ROUND(number [, places])

where number can be any numeric data type (a constant or an expression). If only number is specified, the function rounds to the nearest whole number. If places is also specified, the function rounds to the nearest number with that many decimal places.

The second argument for ROUND must be a constant. See also ROUND_VAR, which accepts non-constant values for the second argument.

The return type is a DECIMAL with its precision and scale adjusted based on the input type of the second argument, if specified. For example, if the type of the first argument is DECIMAL(4,3) and the second argument is 2, the return type will be DECIMAL(3,2):

ROUND(1.234, 2) returns 1.23

Examples ​

Round up to the nearest whole number:

premdb=# select round(2.5) from sys.const;
 round 
-------
    3
(1 row)

Round up with a precision of two decimal places:

premdb=# select round(2.5,2) from sys.const;
 round 
-------
  2.50
(1 row)

The following example rounds the results of an AVG function to a precision of three decimal places.

premdb=# SELECT seasonid, AVG(SUBSTR(ftscore,3,1)::INT), 
ROUND(AVG(SUBSTR(ftscore,3,1)::INT),3) roundgoals FROM match GROUP BY seasonid ORDER BY 2;
 seasonid |            avg            | roundgoals 
----------+---------------------------+------------
      15 | 0.99736842105263157894736 |      0.997
      14 | 1.02368421052631578947368 |      1.024
      13 | 1.06578947368421052631578 |      1.066
       9 | 1.06578947368421052631578 |      1.066
       7 | 1.06842105263157894736842 |      1.068
      18 | 1.07368421052631578947368 |      1.074
       4 | 1.07368421052631578947368 |      1.074
       3 | 1.07792207792207792207792 |      1.078
      17 | 1.07894736842105263157894 |      1.079
       1 | 1.08008658008658008658008 |      1.080
       5 | 1.08157894736842105263157 |      1.082
...

The following example rounds DECIMAL values to the nearest integer.

premdb=# SELECT atid, htid, atid/htid::DEC(7,2), 
ROUND(atid/htid::DEC(7,2)) FROM team;
 atid | htid |  ?column?   | round 
------+------+-------------+-------
   51 |    2 | 25.50000000 |    26
   52 |    3 | 17.33333333 |    17
   53 |    4 | 13.25000000 |    13
   54 |    5 | 10.80000000 |    11
   55 |    6 |  9.16666666 |     9
   56 |    7 |  8.00000000 |     8
   57 |    8 |  7.12500000 |     7
   58 |    9 |  6.44444444 |     6
   59 |   10 |  5.90000000 |     6
   60 |   11 |  5.45454545 |     5
   61 |   12 |  5.08333333 |     5
   62 |   13 |  4.76923076 |     5
   63 |   14 |  4.50000000 |     5
...