Display a Rounded Number With a Specific Scale: Quick Tip

Posted January 25, 2019 by Jim Knicely, Vertica Principal Solution Architect

High angle view of Beijing Guomao.
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/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/Mathematical/ROUND.htm

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

Have fun!