ROUND Number

1. What is the ROUND Function?

The ROUND function in Oracle SQL is used to round a numeric value to a specified number of decimal places or to the nearest integer. The function rounds the number based on standard rounding rules:

• If the digit to the right of the rounding place is 5 or more, the function rounds up.
• If the digit to the right of the rounding place is less than 5, the function rounds down.

2. Syntax of the ROUND Function

The basic syntax of the ROUND function is:

ROUND(number, decimal_places)

• number: The numeric value that you want to round.
• decimal_places: The number of decimal places to round the number to. This is optional. If not provided, the number is rounded to the nearest integer.

3. Parameters

• number: A numeric expression, such as an integer or decimal number, that you want to round.
• decimal_places: A non-negative integer that specifies the number of decimal places to round the number to.
• If this value is positive, the number is rounded to the right of the decimal point.
• If this value is negative, the number is rounded to the left of the decimal point.

4. How Does the ROUND Function Work?

• If decimal_places is positive: The function rounds the number to the specified decimal places.
• If decimal_places is negative: The function rounds the number to the left of the decimal point, meaning it rounds to the nearest ten, hundred, thousand, etc.
• If decimal_places is not specified: The number is rounded to the nearest integer.

5. Examples of Using the ROUND Function

• Example 1: Rounding to the Nearest Integer

If you provide only the number without specifying decimal_places, Oracle rounds the number to the nearest integer.

SELECT ROUND(123.456) FROM dual;

-- Output: 123

Here, 123.456 is rounded to 123.

• Example 2: Rounding to Specific Decimal Places

If you specify decimal_places, Oracle rounds the number to that many decimal places.

SELECT ROUND(123.456, 2) FROM dual;

-- Output: 123.46

Here, 123.456 is rounded to 123.46, as it's rounded to 2 decimal places.

• Example 3: Rounding to the Left of the Decimal Point (Negative decimal_places)

You can round to the left of the decimal point by using a negative value for decimal_places.

SELECT ROUND(123.456, -1) FROM dual;

-- Output: 120

Here, 123.456 is rounded to the nearest 10, which is 120.

• Example 4: Rounding a Negative Number

The ROUND function also works with negative numbers.

SELECT ROUND(-123.456, 1) FROM dual;

-- Output: -123.5

Here, -123.456 is rounded to -123.5.

• Example 5: Rounding to Zero Decimal Places

If you want to round a number to the nearest integer, you can omit the decimal_places parameter.

SELECT ROUND(123.456, 0) FROM dual;

-- Output: 123

Here, 123.456 is rounded to 123 (the nearest integer).

6. How Does ROUND Handle Rounding Rules?

The rounding rules are as follows:

• If the digit in the next place is 5 or more, the number is rounded up.
• If the digit in the next place is less than 5, the number is rounded down.

For example:

• ROUND(123.456, 2) rounds 123.456 to 123.46 because 6 is greater than 5.
• ROUND(123.454, 2) rounds 123.454 to 123.45 because 4 is less than 5.

7. ROUND Function vs TRUNC Function

• ROUND: Rounds the number to a specified number of decimal places, using standard rounding rules.
• TRUNC: Truncates the number by removing digits after a specified number of decimal places, without rounding.

For example:

• ROUND(123.456, 2) returns 123.46.
• TRUNC(123.456, 2) returns 123.45 (no rounding).

8. ROUND Function with Date Values

The ROUND function can also be used with date values, where it rounds the date to the nearest specified unit, such as a day, month, or year.

SELECT ROUND(SYSDATE, 'MM') FROM dual;

-- Output: The current date rounded to the nearest month.

In this case, SYSDATE (the current date) is rounded to the nearest month.

9. Performance Considerations

The ROUND function is generally efficient. However, if you are working with large datasets and performing a lot of rounding operations, performance could be impacted. In such cases, it is advisable to ensure proper indexing and query optimization techniques are applied.

10. Using ROUND in SQL Queries

You can use the ROUND function in SELECT, UPDATE, INSERT, and WHERE clauses in SQL queries.

Example in UPDATE Query:

UPDATE products

SET price = ROUND(price, 2)

WHERE product_id = 101;

This query updates the price of the product with product_id = 101 and rounds it to 2 decimal places.

Example in SELECT Query:

SELECT product_name, ROUND(price, 1) AS rounded_price

FROM products;

This query selects the product name and the price rounded to 1 decimal place.

11. Common Mistakes and Troubleshooting

• Omitting decimal_places: If you don't specify decimal_places, Oracle will round to the nearest integer by default, which could lead to unexpected results.
• Handling Negative Numbers: Ensure that you understand the behavior of the ROUND function when using negative numbers. The number is rounded as per the normal rounding rules, so ROUND(-123.456, 1) gives -123.5.

12. Edge Cases

• Rounding 0: If the number is zero, it stays as zero, even if you specify decimal places.

SELECT ROUND(0, 2) FROM dual;

-- Output: 0

• Rounding large numbers: The ROUND function can handle very large numbers and will round them as expected.