MySQL ROUND Function

MySQL ROUND Function

The ROUND() function in MySQL is used to round a numeric value to a specified number of decimal places. It can round both positive and negative numbers and is commonly used to simplify numbers or format them for display.

Syntax

ROUND(number, decimals)

• number: The number is to be rounded.
• decimals: The number of decimal places to round to. If omitted, the function rounds to the nearest whole number (default is 0).

Key Points

1. If decimals is:

   • Positive: The number is rounded to the specified number of decimal places.
   • Zero: The number is rounded to the nearest whole number.
   • Negative: The number is rounded to the left of the decimal point.

2. If the the decimals argument is omitted, it defaults to 0.

3. The function follows the "round half up" rule:

   • Values of 0.5 or greater are rounded up.
   • Values less than 0.5 are rounded down.

Examples

Example 1: Rounding to Default (Whole Number)

SELECT ROUND(123.456);

Output:

ROUND(123.456)
123

Example 2: Rounding to a Specific Number of Decimal Places

SELECT ROUND(123.456, 2);

Output:

ROUND(123.456, 2)
123.46

Example 3: Rounding Down

SELECT ROUND(123.454, 2);

Output:

ROUND(123.454, 2)
123.45

Example 4: Rounding with Negative Decimals

When the the decimals argument is negative, rounding occurs to the left of the decimal point:

SELECT ROUND(12345.67, -2);

Output:

ROUND(12345.67, -2)
12300

Example 5: Rounding Negative Numbers

SELECT ROUND(-123.456, 2);

Output:

ROUND(-123.456, 2)
-123.46

Example 6: Rounding Without Decimal Places

SELECT ROUND(123.456, 0);

Output:

ROUND(123.456, 0)
123

Use Cases

1. Formatting Output:
   • Displaying numeric results with a specific number of decimal places for better readability.
2. Financial Calculations:
   • Ensuring consistent rounding when dealing with currency or financial figures.
3. Data Aggregation:
   • Simplifying numbers when performing group calculations or summaries.

Conclusion

The ROUND() function is a versatile and essential tool in MySQL for rounding numeric values. Its ability to handle rounding to different decimal places (including left of the decimal point) makes it invaluable for data formatting, calculations, and analysis.