Display a Rounded Number With a Specific Scale

Jim_Knicely · January 2019

The Vertica ROUND function 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).

Example:

dbadmin=> SELECT ROUND(12345.678901726198736271, 2) AS c_rounded;
        c_rounded
--------------------------
12345.680000000000000000
(1 row)

If you’d prefer not to retain the original scale and have the scale of the result be a specific number of decimal places, then explicitly cast the expression to a NUMERIC instead.

dbadmin=> SELECT 12345.678901726198736271::NUMERIC(25, 2) AS c_rounded;
c_rounded
-----------
  12345.68
(1 row)

Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Mathematical/ROUND.htm

https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/DataTypes/Numeric/NUMERIC.htm

Have fun!