MySQL ROUND Function

MySQL ROUND function Mathematical Function is useful to round the specified expression to user-specified precision, or length.

The basic syntax of the ROUND in MySQL is as shown below:

SELECT ROUND(Numeric_Expression)
FROM Source

-- Or
SELECT ROUND(Numeric_Expression, length)
FROM Source
  • Numeric_Expression: It can be a number or a valid numerical expression.
  • Length: This is the precision to which the Expression has to consider. The Length can be a number or a valid numerical expression.
    • If length is a positive number, it display the Numeric_Expression to the number of the decimal point specified as length.
    • If length is a negative number, Expression prints the nearest number to the left side of the decimal point.

To demonstrate this MySQL ROUND Numeric function, we are going to use the below shown data

Numeric Functions Source Table 1

MySQL ROUND Function Example

This MySQL Function is used to return the rounded value of the specified number. The following query shows multiple ways to use this method with positive length and no length.

--  without length on Negative
SELECT ROUND(-20.21) AS `RoundedValue`;

-- without length on Positive Value
SELECT ROUND(125.57) AS `RoundedValue`;

-- along with length on Negative 
SELECT ROUND(-20.21542, 3) AS `RoundedValue`;

-- along with length on Positive Value
SELECT ROUND(1252.12757, 2) AS `RoundedValue`;

--  on string data
SELECT ROUND('105.55') AS `RoundedValue`;

--  on String text
SELECT ROUND('MySQL') AS `RoundedValue`;

From the screenshot below, you can see we used it on different values.

MySQL ROUND Example 3

Let me try Negative Values as the MySQL length, i.e., second argument. And also used the null value in the last statement.

-- with Negative length and Negative numbers
SELECT ROUND(-20.21542, -1) AS `RoundedValue`;

--  with Negative length and Positive numbers
SELECT ROUND(1252.12757, -2) AS `RoundedValue`;

--  with Negative length Example 3
SELECT ROUND(2125.55, -3) AS `RoundedValue`;

--  with NULLS
SELECT ROUND(NULL, 3) AS `RoundedValue`;
MySQL ROUND Example 2

MySQL ROUND Example 2

This Numeric method also allows you to find the rounded values for the column data. In this example, we are going to find the nearest values for all the records present in the Standard Cost, Sales, tax Amount, and Service Grade columns.

SELECT Product, Color,
		StandardCost, ROUND(StandardCost, 3) AS Cost, 
        Sales, ROUND(Sales, 1) AS Sales, 
        TaxAmt, ROUND(TaxAmt, 2) AS Tax,
        ServiceGrade, ROUND(ServiceGrade) AS Grade
FROM `numeric functions`;
MySQL ROUND Example 4

In this example, we reproduce the same query that we used in our previous example. But, this time, we use the negative values as the second argument for the Mathematical Function.

SELECT Product, Color,
		StandardCost, ROUND(StandardCost, -1) AS Cost, 
        Sales, ROUND(Sales, -2) AS Sales, 
        TaxAmt, ROUND(TaxAmt, -3) AS Tax,
        ServiceGrade, ROUND(ServiceGrade) AS Grade
FROM `numeric functions`;
MySQL ROUND Example 5