MySQL Round

ROUND() function of MySQL rounds the number to the upwards or downwards to the nearest whole number or to the specified decimal places.

MySQL Round

MySQL Round

     

ROUND() function of MySQL rounds the number to the upwards or downwards to the nearest whole number or to the specified decimal places. For getting the whole rounded number ROUND() function uses "round half up". "round half up" means that if the number's fractional part is greater than or equals to "5" then it will be rounded to next whole number value if the number is positive and to the whole negative number value if it is a negative number.

Syntax :

ROUND( number, [Decimal Places]);

Rounds the argument number to Decimal Places position where Decimal Places is optional.

Explanation:

Suppose we apply the ROUND() function on the positive integer value 123.34

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

Here you can see that 123.34 is rounded to 123.

If we apply the ROUND() function on the positive integer value 123.56

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

It returns the value 124 since .56 when rounded produces result incremented by one number in 123.

If we apply the ROUND() function on same number in negative

mysql> SELECT ROUND(-123.56);
+----------------+
| ROUND(-123.56) |
+----------------+
|           -124 |
+----------------+
1 row in set (0.02 sec)

In above, the number reaches to the next whole number.

If we apply the second version of ROUND() function to the 123.45678 which takes one more argument for decimal position.

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

Here you can see that we have provided one more argument into the ROUND() which describes that we have to round the number up to the 2 decimal places and since 3rd number is greater than five therefore the second place digit is rounded and results 6 and as a whole it returns the result 123.46.

We can also apply this ROUND() function to the number provided into the Exponential format (e.g 1234E1 or 1234E-1).

mysql> SELECT ROUND(1236E-1,2);
+------------------+
| ROUND(1236E-1,2) |
+------------------+
|           123.60 |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT ROUND(1236E-2,2);
+------------------+
| ROUND(1236E-2,2) |
+------------------+
|            12.36 |
+------------------+
1 row in set (0.00 sec)