MySQL ROUND Function

Summary: in this tutorial, you will learn how to use the MySQL ROUND() function to round a number to a specified number of decimal places.

Introduction to MySQL ROUND() function

The ROUND() is a mathematical function that allows you to round a number to a specified number of decimal places.

The following shows the syntax of the ROUND() function:

ROUND(n,[d])

In this syntax, n is a number to be rounded and d is the number of decimal places to which the number is rounded.

The number of decimal places ( d) is optional. It defaults to zero if you skip it.

The following statements are equivalent:

SELECT ROUND(20.5);
-- 21
SELECT ROUND(20.5, 0);
-- 21

The number of decimal places ( d) can be positive or negative. If it is negative, then the d digits left of the decimal point of the number n becomes zero.

SELECT ROUND(121.55,-2)
-- 100

The ROUND() the function returns a number which has the same data type as the number to be rounded (n)

Rounding exact-value number rules

If n is an exact-value number, the ROUND() the function uses the “round toward nearest” rule.

It means the value of the fractional part of .5 or greater is rounded up to the next integer if positive or rounded down to the next integer if negative.

SELECT ROUND(10.5); 
-- 11
SELECT ROUND(10.6); 
-- 11

SELECT ROUND(-10.5); 
-- -11
SELECT ROUND(-10.6);
-- -11

The value of the fractional part less than .5 is rounded down to the next integer if positive or up to the next integer if negative.

SELECT ROUND(10.4);
-- 10
SELECT ROUND(-10.4);
-- -10

Rounding approximate-value number rules

When rounding an approximate-value number, the result of the ROUND() function depends on the C library. Typically, it uses the “round to nearest even” rule i.e., it rounds a value with a fractional part exactly halfway between two integers to the nearest even integer.

MySQL ROUND() function examples

We will use the orderDetails table from the sample database for the demonstration.

The following statement finds the average order line item values by product codes:

SELECT 
    productCode,
    AVG(quantityOrdered * priceEach) avg_order_item_value
FROM
    orderDetails
GROUP BY 
    productCode;    

Here is the output:

The average order values of products are not quite readable because it contains many numbers after the decimal points.

For the average values, the number after decimal points may not be important. Therefore, you can use the ROUND() function to round them to zero decimal places as shown in the following query:

SELECT 
    productCode,
    ROUND(AVG(quantityOrdered * priceEach)) avg_order_item_value
FROM
    orderDetails
GROUP BY 
    productCode;    

The following picture shows the output:

In this tutorial, you have learned how to use the MySQL ROUND() function to round a number to a specified number of decimal places.