sysuser sysuser - 5 months ago 13
SQL Question

Oracle SQL: Returning sum query results as a decimal with precision

I see that I need to return the sum of a column in Oracle for which the query I use is the following

select sum(scores) from table_x;


I want to return the results always like this

If the result is an integer, say 1000, the output should be 1000.00 (by default 2 precision digits for the fractional part)

If the result is a decimal with n digits precision, the result will should be returned as it is for example, if sum = 1000.555666, then I want the result to be 1000.555666.

The problem is when I modify the query to use cast like

select cast(sum(scores) as binary_double) from table_x


When the sum is 1000, I get the result as 1000.0, but I want one more zero in the fractional part (1000.00) since I do string comparison of results. Tried numerous solutions like casting to different data types, but can't get the integer results in the form I want.

Answer

Try:

select to_char( sum(scores), 'FM9999999999D009999' )
from table_x;

Please refer to the documentation of to_char function for details ==> click