Skip to main content
Documents
Share
LinkedIn
Facebook
Twitter
Copy
ClausesStatementsKeywordsExpressionsFunctionsIndexesPerformance Optimization

MySQL ROUND() Function

The `ROUND()` function in MySQL is used to round a numeric value to a specified number of decimal places. It is particularly useful in scenarios where precision is required for financial calculations, reporting, or data analysis.

Usage

The `ROUND()` function is commonly used to control the number of decimal places in a numeric output, enhancing readability and precision. It can be applied in `SELECT` statements, among others, for rounding purposes.

ROUND(number, decimals)

In this syntax, `number` is the numeric value to be rounded, and `decimals` is the number of decimal places to round to. If `decimals` is omitted, the number is rounded to the nearest integer. `ROUND()` follows standard rounding rules, rounding towards the nearest value. For midpoint values (e.g., `0.5`), it rounds away from zero.

Examples

1. Basic Rounding

SELECT ROUND(123.456);

This example rounds the number `123.456` to the nearest integer, resulting in `123`.

2. Specifying Decimal Places

SELECT ROUND(123.456, 2);

Here, the number `123.456` is rounded to two decimal places, yielding `123.46`.

3. Rounding Negative Numbers

SELECT ROUND(-123.456, 1);

In this example, the negative number `-123.456` is rounded to one decimal place, resulting in `-123.5`.

4. Rounding with Negative Decimals

SELECT ROUND(12345.6789, -2);

This rounds `12345.6789` to the nearest hundred, resulting in `12300`.

Tips and Best Practices

  • Specify decimal places carefully. Always specify the number of decimal places to avoid unintended rounding.
  • Consider data type limitations. Be aware of how `ROUND()` interacts with different MySQL data types (e.g., `FLOAT`, `DOUBLE`, `DECIMAL`) to prevent precision loss.
  • Use in data formatting. Utilize `ROUND()` for data presentation where consistent precision is necessary.
  • Combine with other functions. Combine `ROUND()` with aggregate functions like `SUM()` or `AVG()` to ensure precise result formatting, for example:
  • SELECT ROUND(AVG(salary), 2) FROM employees;
  • Performance considerations. While `ROUND()` is efficient, applying it to large datasets can have a performance impact. Optimize queries when necessary.