ROUND
Round a number (up or down) to the nearest integer. Optionally, specify a decimal precision for the rounding.
ROUND(number [, places])
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 of precision.
The first argument can be any numeric data type. However, if both arguments are used, the first argument cannot be a floating-point data type. If you want to round a floating-point number to a specific decimal precision, you must explicitly cast the number to a decimal data type. For example:
premdb=# create table floats (f1 float8);
CREATE TABLE
premdb=# insert into floats values(1234567890.12334567890);
INSERT 0 1
premdb=# select round(f1,3) from floats;
ERROR: function round(double precision, integer) does not exist
LINE 2: select round(f1,3) from floats;
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
premdb=# select round(f1::decimal(19,5),3) from floats;
round
----------------
1234567890.123
(1 row)
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
...