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])
Code language: SQL (Structured Query Language) (sql)
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
Code 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)
-- 100
Code language: SQL (Structured Query Language) (sql)
The ROUND() 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() 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— -11
SELECT ROUND(-10.6);
— -11
Code 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)
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;
Code language: SQL (Structured Query Language) (sql)
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.