MySQL Tutorial/Math Numeric Functions/ROUND

How rounding differs for exact and approximate values:

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

ROUND(

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

ROUND(

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

ROUND(150.000,2)/ROUND(150,2)

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

ROUND(23.298, -1)

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

Rounding value in a column

mysql>
mysql> create table myTable(
    ->   id           int(2),
    ->   value        FLOAT(6,2)
    -> );
Query OK, 0 rows affected (0.16 sec)
mysql>
mysql> insert into myTable(ID,  value)values (1,9);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (2,2.11);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (3,3.44);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (4,-4.21);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (5,10);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (6,3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (7,-5.88);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (8,123.45);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (9,98.23);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (10,938.23);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (11,984.23);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (12,198.23);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (13,928.87);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (14,25.37);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (15,918.3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (16,9.23);
Query OK, 1 row affected (0.02 sec)
mysql> insert into myTable(ID,  value)values (17,8.23);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from myTable;
+------+--------+
| id   | value  |
+------+--------+
|    1 |   9.00 |
|    2 |   2.11 |
|    3 |   3.44 |
|    4 |  -4.21 |
|    5 |  10.00 |
|    6 |   3.00 |
|    7 |  -5.88 |
|    8 | 123.45 |
|    9 |  98.23 |
|   10 | 938.23 |
|   11 | 984.23 |
|   12 | 198.23 |
|   13 | 928.87 |
|   14 |  25.37 |
|   15 | 918.30 |
|   16 |   9.23 |
|   17 |   8.23 |
+------+--------+
17 rows in set (0.00 sec)
mysql>
mysql>
mysql> select value, ROUND(value) from myTable;
+--------+--------------+
| value  | ROUND(value) |
+--------+--------------+
|   9.00 |            9 |
|   2.11 |            2 |
|   3.44 |            3 |
|  -4.21 |           -4 |
|  10.00 |           10 |
|   3.00 |            3 |
|  -5.88 |           -6 |
| 123.45 |          123 |
|  98.23 |           98 |
| 938.23 |          938 |
| 984.23 |          984 |
| 198.23 |          198 |
| 928.87 |          929 |
|  25.37 |           25 |
| 918.30 |          918 |
|   9.23 |            9 |
|   8.23 |            8 |
+--------+--------------+
17 rows in set (0.00 sec)
mysql>
mysql> drop table myTable;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql>

ROUND(X), ROUND(X,D) rounds the argument X to D decimal places.

1. D defaults to 0 if not specified.
2. D can be negative to cause D digits left of the decimal point of the value X to become zero.

mysql>
mysql> SELECT ROUND(-1.23);
+--------------+
| ROUND(-1.23) |
+--------------+
|           -1 |
+--------------+
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.58);
+-------------+
| ROUND(1.58) |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)