In MySQL, the ROUND() function allows you to round a number up or down to a given number of decimal places.

The way it works is, you provide the number as an argument, and you have the option of choosing how many decimal places to round the number to. If you don’t specify the number of decimal places, the result will have no decimal places.

Syntax

This function can be used in either of the following ways:

ROUND(X)
ROUND(X,D)

Where X is the number and D is the number of decimal places you want it rounded to. If you omit D, the number will be rounded to zero decimal places.

Also, D can be negative. This results in D digits left of the decimal point of the value X to become zero.

Example 1 – Using One Argument

Here’s a basic example to demonstrate what happens when we don’t provide a second argument.

SELECT ROUND(1.49) Result;

Result:

+--------+
| Result |
+--------+
|      1 |
+--------+

In this case the number is rounded down to the nearest integer.

If we increase the fractional part, here’s what happens:

SELECT ROUND(1.50) Result;

Result:

+--------+
| Result |
+--------+
|      2 |
+--------+

In this case the number is rounded up instead.

Example 2 – Using Two Arguments

Here’s how we can use a second argument to specify the decimal places to round the number to.

SELECT ROUND(1.234, 2) Result;

Result:

+--------+
| Result |
+--------+
|   1.23 |
+--------+

And here’s what happens when I increase the last digit to 5:

SELECT ROUND(1.235, 2) Result;

Result:

+--------+
| Result |
+--------+
|   1.24 |
+--------+

Of course, we could use many more decimal places. For example, we could trim a lot of decimal places down.

SELECT ROUND(1.23456789123456789, 8) Result;

Result:

+------------+
| Result     |
+------------+
| 1.23456789 |
+------------+

Or we could increase the number of decimal places.

SELECT ROUND(1.234, 8) Result;

Result:

+------------+
| Result     |
+------------+
| 1.23400000 |
+------------+

Example 3 – Using a Negative Value for the Second Argument

Here’s how we can use a negative value on the second argument to cause parts of the non-fractional part to be rounded to zero.

SELECT ROUND(123.456, -1) Result;

Result:

+--------+
| Result |
+--------+
|    120 |
+--------+

And here’s how it gets rounded if we increase the 3 to 5 or more.

SELECT ROUND(125.456, -1) Result;

Result:

+--------+
| Result |
+--------+
|    130 |
+--------+

Example 4 – Rounding Negative Numbers vs Positive Numbers

As seen in the previous examples, when using positive numbers, a value with a fractional part of .5 or greater is rounded up to the next integer.

However, when using negative numbers, such values are rounded down.

SELECT 
  ROUND(1.50) Positive,
  ROUND(-1.50) Negative;

Result:

+----------+----------+
| Positive | Negative |
+----------+----------+
|        2 |       -2 |
+----------+----------+

Rounding Approximate-Value Numbers

The above rounding rule only applies to exact-value numbers. For approximate-value numbers, the result depends on the C library. The MySQL documentation states this about approximate-value numbers:

On many systems, 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.

And here’s the example used on that page:

SELECT 
  ROUND(2.5), 
  ROUND(25E-1);

Result:

+------------+--------------+
| ROUND(2.5) | ROUND(25E-1) |
+------------+--------------+
|          3 |            2 |
+------------+--------------+