Rounding the ROUND Function

[JimKnicely]

The SQL ROUND (X, Y) built-in function rounds the argument X to Y decimal places.

In most RDMBS systems the ROUND function will return the same result regardless if Y is an integer or a float.

MySQL:

Code: Select all

MySQL> SELECT ROUND(123.45678, 3);
+---------------------+
| ROUND(123.45678, 3) |
+---------------------+
|             123.457 |
+---------------------+
1 row in set (0.00 sec)

MySQL> SELECT ROUND(123.45678, 3.0);
+-----------------------+
| ROUND(123.45678, 3.0) |
+-----------------------+
|               123.457 |
+-----------------------+
1 row in set (0.00 sec)

SQL Server:



Oracle:

Code: Select all

SQL> SELECT ROUND(123.45678, 3) FROM dual;

ROUND(123.45678,3)
------------------
           123.457

SQL> SELECT ROUND(123.45678, 3.0) FROM dual;

ROUND(123.45678,3.0)
--------------------
             123.457

Vertica:

Being the new kid on the block, Vertica had to mix things up a bit. As expected, the Vertica ROUND function rounds a value to a specified number of decimal places; however, it will retain the original scale and precision if Y is an integer and truncate the precision if Y is a float.

Code: Select all

dbadmin=> SELECT ROUND(123.45678, 3);
   ROUND
-----------
123.45700
(1 row)

dbadmin=> SELECT ROUND(123.45678, 3.0);
  ROUND
---------
123.457
(1 row)

The distinction is really noticeable when there are many decimal places in X:

Code: Select all

dbadmin=> SELECT ROUND(123.4567890123456789012345678901234567890, 5);
                   ROUND
-------------------------------------------
123.4567900000000000000000000000000000000
(1 row)

dbadmin=> SELECT ROUND(123.4567890123456789012345678901234567890, 5.0);
   ROUND
-----------
123.45679
(1 row)

Keep this in mind when writing SQL which includes the ROUND function for reports. You probably don’t want to display the value “123.4567900000000000000000000000000000000” is a report for the accounting folks

Have fun!