MySQL ROUND()

ROUND(X), ROUND(X, D)

 

Rounds the argument X to D decimal places. The rounding algorithm depends on the data type of X. D defaults to 0 if not specified. D can be negative to cause D digits left of the decimal point of the value X to become to zero.

 

mysql> SELECT ROUND(-1.23);
+--------------+
| ROUND(-1.23) |
+--------------+
|           -1 |
+--------------+
1 row in set (0.10 sec)

 

mysql> SELECT ROUND(-1.58);
+--------------+
| ROUND(-1.58) |
+--------------+
|           -2 |
+--------------+
1 row in set (0.00 sec)

 

mysql> SELECT ROUND(1.58) ;
+-------------+
| ROUND(1.58) |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(1.298, 1);
+-----------------+
| ROUND(1.298, 1) |
+-----------------+
|             1.3 |
+-----------------+
1 row in set (0.00 sec)


mysql> SELECT ROUND(1.298, 0);
+-----------------+
| ROUND(1.298, 0) |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.01 sec)


mysql> SELECT ROUND(23.298, -1);
+-------------------+
| ROUND(23.298, -1) |
+-------------------+
|                20 |
+-------------------+
1 row in set (0.00 sec)

The return value has the same type as the first argument(assuming that it is integer, double or decimal). This means that for an integer argument, the result is an integer(no decimal places):

 

mysql> SELECT ROUND(150.000, 2), ROUND(150, 2);
+-------------------+---------------+
| ROUND(150.000, 2) | ROUND(150, 2) |
+-------------------+---------------+
|            150.00 |           150 |
+-------------------+---------------+
1 row in set (0.00 sec)

 

ROUND() uses the following rules depending on the type of the first argument:

• For exact-value numbers, ROUND() uses the "round half away from zero" or "round toward nearest" rule: A value with a fractional part of .5 or greater is rounded up to the next integer if positive or down to the next integer if negative.(In other words, it is rounded away from zero.) A value with a fractional part less than .5 is rounded down to the next integer if positive or up to the next integer if negative
• For approximate-value numbers, the result depends on the C library. On many system, this means that ROUND() uses the "round to nearest even" rule : A value with a fractional part exactly halfway between two integers is rounded to the nearest even integer

Tho following example shows how rounding differs for exact and approximate values:

mysql> SELECT ROUND(2.5), ROUND(25E-1);
+------------+--------------+
| ROUND(2.5) | ROUND(25E-1) |
+------------+--------------+
|          3 |            2 |
+------------+--------------+
1 row in set (0.00 sec)