How To Use ROUND In Oracle SQL

How To Use ROUND In Oracle SQL

October 16, 2019
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

This help is based on examples so it would be easier to understand. Oracle Round function allows to round a number value to a “N” places up before decimal place or after it. The syntax of Oracle Round is following:

ROUND (<number> [,<rounding_integer>]);

The first Oracle Round example has input value 12.3333333 and it will round the number up to the decimal place and the function will return an integer value (number 12). This way works the Oracle rounding function when the “rounding integer” has left out and it does remove all numbers after decimal place by default mode.

 SELECT ROUND (12.3333333) AS rounded
   FROM dual;

oracle dba oracle database management database management system database oracle performance tuning oracle round oracle round in oracle sql round oracle sql round in oracle sql learn sql learn

To leave some numbers after the decimal place use a positive number in the “rounding integer” place. The following example has the same input number as on the last example and the rounding integer is set 2. The output shows the 12.33 value that is exactly two numbers after the decimal place.

 SELECT ROUND (12.3333333,2) AS rounded
   FROM dual;

oracle dba oracle database management database management system database oracle performance tuning oracle round oracle round in oracle sql round oracle sql round in oracle sql learn sql learn

To round up before the decimal place use a negative number in the “rounding integer” place. This example we will round only 1 place up of value 12.3333333 and the output value has become number 10. If the input values would have been 16 then the output would have number 20 now.

 SELECT ROUND (12.3333333,-1) AS rounded
   FROM dual;

oracle dba oracle database management database management system database oracle performance tuning oracle round oracle round in oracle sql round oracle sql round in oracle sql learn sql learn

The following example has almost the same query as the last one – only this time we will round up 2 places before decimal place. The “rounding integer” has set “-2” and the number 12.3333333 has become 0.

 SELECT ROUND (12.3333333,-2) AS rounded
   FROM dual;

oracle dba oracle database management database management system database oracle performance tuning oracle round oracle round in oracle sql round oracle sql round in oracle sql learn sql learn

The SQL query below does rounding with a dynamic value and it comes from pseudo-column named “Rownum“. Oracle Rownum value grows with every next line and that since it has been used as “rounding integer” than leaves more decimal places to available with every next line until it becomes a full value.

 SELECT ROUND (12.3333333,rownum) AS rounded
   FROM dual
    CONNECT BY rownum < 8;

oracle dba oracle database management database management system database oracle performance tuning oracle round oracle round in oracle sql round oracle sql round in oracle sql learn sql learn

The output shows how the rounding leaves more number available after the decimal place with growing Oracle Rownum value.

See Also:
Oracle Select Oracle Rownum Oracle Home

Leave a Reply

Your email address will not be published. Required fields are marked *

*