The MySQL round function rounds a number up or down to the nearest whole number or to the specified number of decimal places. When rounding off to a whole number, MySQL uses the "round half up" or "round towards nearest" rule.
This means that if the fractional part of the number is greater than or equal to 0.5, the number will be rounded up to the next integer in the case of a positive number, and down to the next integer in the case of a negative number. In other words, the number will be rounded away from zero if the fractional portion exceeds 0.5.
In the tutorial below, we will look at the MySQL round function in detail with many examples.
The syntax for the MySQL round function is as follows:
The variable number is the actual number that you will be rounding off. The variable decimal_places is optional and specifies the number of decimal places to round the number to. If the amount of decimal places is not specified, MySQL defaults to rounding it to 0 decimal places.
You can also use negative numbers when telling MySQL how to round. In this case, rounding will cause that many digits left of the decimal point to become zero. In other words, you can use this to round to the nearest ten, hundred, thousand, and so on.
The following examples show how to use the MySQL round function in most of the ways it can be used:
Below we are rounding 5.65 to the nearest whole number
SELECT ROUND(5.65); -- this gives 6
Below we are rounding 5.49 to the nearest whole number
SELECT ROUND(5.49); -- this gives 5
Below we are rounding 5.635 to 2 decimal places
SELECT ROUND(5.635, 2); -- this gives 5.64
Below we are rounding -5.4 to the nearest whole number
SELECT ROUND(-5.4); -- this gives -5
Below we are rounding -5.9436 to 3 decimal places
SELECT ROUND(-5.9436, 3); -- this gives -5.944
Below we are rounding 155.8 to the nearest 10
SELECT ROUND(155.8, -1); -- this gives 160
Below we are rounding 154 to the nearest 10
SELECT ROUND(154, -1); -- this gives 150
Below we are rounding 72,345 to the nearest 1000
SELECT ROUND(72345, -3); -- this gives 72000
Try the examples above for yourself and see how they work.
We can also specify numbers to the rounded using the exponential format and it works fine:
Here we are rounding 72345E-2 (723.45) to the nearest 100:
SELECT ROUND(72345E-2, -2); -- this gives 700
The return type that the round function gives is the same as the number type that was passed to the function. For example, if you passed an integer to be rounded, you will get back an integer as the result. The same applies if you passed a double or decimal.
That is it for this tutorial on the MySQL Round Function. We hope you found it useful.