MySQL Round() Function

Number rounding refers to finding the approximate nearby value of a given numerical value. Number rounding is a common practice when working with numerical values, allowing you to get a numerical value that is easier to read and parse.

In this tutorial, you will learn about MySQL’s round() function. We will cover what the function does, its function syntax, accepted parameters, return values, and the practical examples of the function usage.

MySQL Round() Function

In MySQL, the round() function in MySQL allows you to round off a numerical value to a specified number of decimal places.

The following shows the syntax of the round function in MySQL:

ROUND(number, decimal_places)

The function accepts two main arguments:

The number to be rounded off.
Decimal_places – This parameter specifies the number of decimal places to which the input number is rounded. This an optional parameter. If it is missing, the function rounds off the number to the nearest whole number.

Take a look at the following example:

SELECT ROUND(3.14159);
-- 3
SELECT ROUND(3.14159, 0) as p;
- 3

As you can see from the output, setting the decimal_places parameter to 0 or omitting it is very exact. Both return the value to the nearest whole number.

Example 1: Using the Round() Function with Positive Decimal Value

This example shows the resulting value when the decimal_places parameter value is set to a positive integer:

SELECT ROUND(3.14159, 0) as p;

Output:

p |
-----+
3.142|

Example 2: Using the Round() Function with Negative Decimal

We can also set the decimal_places parameter to a negative value as shown in the following:

SELECT ROUND(3.14159, -3) as p;

This should force the function to round of the value before the decimal.

Result:

p|
-+
0|

Example 2:

SELECT ROUND(314159.14159, -3) as p;

Result:

p |
------+
314000|

Example 3: Using the Round() Function in a Table

We can also use the round() function in a table column as shown in the following:

Round the values as follows:

select value, ROUND(value) as roundoff from random_ints ri ;

Output:

Conclusion

MySQL’s round() function is a valuable tool for approximating the numbers to a specified level of precision. It can be used in various mathematical and statistical operations and is often used to simplify the calculations and make them more manageable. The round() function is easy to use and can be a valuable addition to your MySQL toolkit.

MySQL Round() Function