ROUND

Rounds a value to a specified number of decimal places, retaining the original precision and scale. Fractions greater than or equal to .5 are rounded up. Fractions less than .5 are rounded down (truncated).

Behavior Type

Immutable

Syntax

ROUND ( expression [ , places ] )

Parameters

expression

Is an expression of type NUMERIC or DOUBLE PRECISION (FLOAT).

 
places

An INTEGER value. When places is a positive integer, Vertica rounds the value to the right of the decimal point using the specified number of places. When places is a negative integer, Vertica rounds the value on the left side of the decimal point using the specified number of places.

Notes

Using ROUND with a NUMERIC datatype returns NUMERIC, retaining the original precision and scale.

=> SELECT ROUND(3.5);
 ROUND
-------
   4.0
(1 row)

Examples

=> SELECT ROUND(2.0, 1.0) FROM dual;
 ROUND
-------
   2.0
(1 row)
=> SELECT ROUND(12.345, 2.0);
 ROUND
--------
 12.350
(1 row)
=> SELECT ROUND(3.444444444444444);
       ROUND
-------------------
 3.000000000000000
(1 row)
=> SELECT ROUND(3.14159, 3);
  ROUND
---------
 3.14200
(1 row)
=> SELECT ROUND(1234567, -3);
  ROUND
---------
 1235000
(1 row)

=> SELECT ROUND(3.4999, -1);
 ROUND
--------
 0.0000
(1 row)

The following example creates a table with two columns, adds one row of values, and shows sample rounding to the left and right of a decimal point.

=> CREATE TABLE sampleround (roundcol1 NUMERIC, roundcol2 NUMERIC);
CREATE TABLE

=> INSERT INTO sampleround VALUES (1234567, .1234567);
 OUTPUT
--------
      1
(1 row)

=> SELECT ROUND(roundcol1,-3) AS pn3, ROUND(roundcol1,-4) AS pn4, ROUND(roundcol1,-5) AS pn5 FROM sampleround;

           pn3           |           pn4           |           pn5
-------------------------+-------------------------+-------------------------
 1235000.000000000000000 | 1230000.000000000000000 | 1200000.000000000000000
(1 row)

=> SELECT ROUND(roundcol2,3) AS p3, ROUND(roundcol2,4) AS p4, ROUND(roundcol2,5) AS p5 FROM sampleround;
				
        p3         |        p4         |        p5
-------------------+-------------------+-------------------
 0.123000000000000 | 0.123500000000000 | 0.123460000000000
(1 row)