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.

Here’s the basic syntax of the ROUND() function:

ROUND(n,[d])Code language: SQL (Structured Query Language) (sql)

In this syntax:

• n is a number to be rounded
• 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);
-- 21Code language: SQL (Structured Query Language) (sql)

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)
-- 100Code language: SQL (Structured Query Language) (sql)

The ROUND() function returns a number that 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() 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. For example:

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

SELECT ROUND(-10.5); 
-- -11
SELECT ROUND(-10.6);
-- -11Code language: SQL (Structured Query Language) (sql)

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
Code language: SQL (Structured Query Language) (sql)

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;    
Code language: SQL (Structured Query Language) (sql)

Output:

The average order values of products are not quite readable because they contain 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;    
Code language: SQL (Structured Query Language) (sql)

The following picture shows the output:

Summary

• Use the MySQL ROUND() function to round a number to a specified number of decimal places.