MySQL ROUND Function

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

MySQL ROUND Syntax

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 rounded. Length can be a number or a valid numerical expression.
    • If length is a positive number, it rounds the Numeric_Expression to the number of the decimal point specified as length.
    • If length is a negative number, Expression rounded to the left side of the decimal point.

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

MySQL ROUND Function 1

MySQL ROUND Function Example 1

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

-- MySQL ROUND Function Example

-- Round Function without length on Negative Value
SELECT ROUND(-20.21) AS `Rounded Value`;

-- Round Function without length on Positive Value
SELECT ROUND(125.57) AS `Rounded Value`;

-- Round Function along with length on Negative Value
SELECT ROUND(-20.21542, 3) AS `Rounded Value`;

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

-- Round Function on string value
SELECT ROUND('105.55') AS `Rounded Value`;

-- Round Function on String
SELECT ROUND('MySQL') AS `Rounded Value`;

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

MySQL ROUND Example 3

Let me try Negative Values as the MySQL length (second argument)

-- Round Function with Negative length and Negative Value
SELECT ROUND(-20.21542, -1) AS `Rounded Value`;

-- Round Function with Negative length and Positive Value
SELECT ROUND(1252.12757, -2) AS `Rounded Value`;

-- Round Function with Negative length Example 3
SELECT ROUND(2125.55, -3) AS `Rounded Value`;

-- Round Function with NULLS
SELECT ROUND(NULL, 3) AS `Rounded Value`;
MySQL ROUND Example 2

MySQL ROUND Example 2

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

-- MySQL ROUND Function Example
USE mysqltutorial;
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

ROUND Example 3

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 ROUND Mathematical Function.

-- MySQL ROUND Function Example
USE mysqltutorial;
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