MySQL ROUND() function explained

The MySQL ROUND() function is used to round a decimal number to the nearest integer (whole) number.

When the decimal value is .5 or higher, the function will round your number up.

When the decimal value is lower than .5 then the function will round your number down.

Here’s an example of the ROUND() function in action:

SELECT ROUND(7.5), ROUND(7.499);

The output of the query above will be as follows. Notice how 7.5 rounds up to 8 while 7.499 rounds down to 7:

+------------+--------------+
| ROUND(7.5) | ROUND(7.499) |
+------------+--------------+
|          8 |            7 |
+------------+--------------+

The ROUND() function can round both a positive number and a negative number.

When you pass a negative number to the function, then the number will be rounded down when the decimal place is higher than .5 as shown below:

mysql> SELECT ROUND(-7.5), ROUND(-7.499);
+-------------+---------------+
| ROUND(-7.5) | ROUND(-7.499) |
+-------------+---------------+
|          -8 |            -7 |
+-------------+---------------+

The full syntax of the ROUND() function is as shown below:

ROUND(number, decimals);

The ROUND() function accepts two arguments:

• The number to round as the first parameter
• And the number of decimal places to round the number to

If you omit the decimal places, then the specified number will be rounded to zero decimal places (integer or whole number)

When the decimal places of your number are less than the specified decimals argument, then the function will do nothing to the given number.

Take a look at the following example. Notice how the first two numbers are unchanged by the function:

mysql> SELECT ROUND(7.5, 2), ROUND(7.49, 2), ROUND(7.299, 2);
+---------------+----------------+-----------------+
| ROUND(7.5, 2) | ROUND(7.49, 2) | ROUND(7.299, 2) |
+---------------+----------------+-----------------+
|           7.5 |           7.49 |            7.30 |
+---------------+----------------+-----------------+

Finally, you can pass the name of your MySQL table column to the function to round the numbers stored in that column.

To show you what I mean, imagine you have a scores table in your MySQL database with the following data:

mysql> SELECT * FROM scores;
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | Jack  |   4.9 |
|  2 | Susan |   7.4 |
|  3 | Mark  |  6.73 |
|  4 | Fany  |  9.15 |
+----+-------+-------+

When you need to round the score column values, then you can pass the score column as the first argument to the ROUND() function in your SQL query.

The following example query:

SELECT name, score, ROUND(score) AS `rounded score` FROM scores;

Will produce the following result:

+-------+-------+---------------+
| name  | score | rounded score |
+-------+-------+---------------+
| Jack  |   4.9 |             5 |
| Susan |   7.4 |             7 |
| Mark  |  6.73 |             7 |
| Fany  |  9.15 |             9 |
+-------+-------+---------------+

The rounded value of each score value is returned next to the original number.

And that’s how the MySQL ROUND() function works. You can round any floating or decimal numbers using the function.

Feel free to use any MySQL query provided in this tutorial 😉