sysuser - 1 year ago 55

SQL Question

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

Source (Stackoverflow)