CODEWITHSUNDEEP
sqloracle-database
sysuser
sysuser

Reputation: 1100

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.

Upvotes: 0

Views: 6561

Answers (1)

krokodilko
krokodilko

Reputation: 36087

Try:

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

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

Upvotes: 2

Related Questions