Adding decimal places in a select query to a sum

Question

I have built a select query which utilises the SUM function and I'm trying to make the output show with 2 decimal places for the Settlement amount. In this example the actual STTL_AMOUNT is 29999 but I'd like it to output 299.99 so I need to add a decimal place in before the last 2 numbers. The format for STTL_AMOUNT is number(22,4)

SELECT COUNT (STTL_AMOUNT) as "COUNT",
   SUM(STTL_AMOUNT) - SUM(CASE WHEN IS_REVERSAL =1 THEN STTL_AMOUNT ELSE 0 End ) as "Settlement Amount",
   HOST_DATE as "Settlement Date"
   FROM  OPR_OPERATION
   WHERE    MCC = '6011'
   AND      MSG_TYPE ='MSGTPRES'
   AND      HOST_DATE >= TRUNC( SYSDATE )
   GROUP BY HOST_DATE;

And I get the output below:

"COUNT" "Settlement Amount" "Settlement Date"
1   29999   24-MAY-24

But I would like it to output as this:

"COUNT" "Settlement Amount" "Settlement Date"
1   299.99  24-MAY-24

I was wondering if I should use to_char, but I can't seem to get the query to work at all if I try that.

Answer 1

You should be able to simplify your query by swapping the THEN and ELSE clauses in the CASE expression and dividing by 100 to:

SELECT COUNT (STTL_AMOUNT) as "COUNT",
       SUM(CASE WHEN IS_REVERSAL = 1 THEN 0 ELSE STTL_AMOUNT/100 END)
         as "Settlement Amount",
       HOST_DATE as "Settlement Date"
FROM   OPR_OPERATION
WHERE  MCC = '6011'
AND    MSG_TYPE ='MSGTPRES'
AND    HOST_DATE >= TRUNC( SYSDATE )
GROUP BY HOST_DATE;

The division should be able to inside or outside the CASE or SUM:

SUM(CASE WHEN IS_REVERSAL = 1 THEN 0 ELSE STTL_AMOUNT END/100)

or

SUM(CASE WHEN IS_REVERSAL = 1 THEN 0 ELSE STTL_AMOUNT END)/100

Which, for the sample data:

CREATE TABLE opr_operation (host_date, sttl_amount, is_reversal, mcc, msg_type) AS
  SELECT SYSDATE, 29999, 0, '6011', 'MSGTPRES' FROM DUAL UNION ALL
  SELECT SYSDATE - INTERVAL '1' SECOND, 20000, 0, '6011', 'MSGTPRES' FROM DUAL;

Should all output:

COUNT	Settlement Amount	Settlement Date
1	299.99	2024-05-24 21:23:20
1	200	2024-05-24 21:23:19

If you want to display the value with two decimal places then use TO_CHAR:

SELECT COUNT (STTL_AMOUNT) as "COUNT",
       TO_CHAR(
         SUM(CASE WHEN IS_REVERSAL = 1 THEN 0 ELSE STTL_AMOUNT/100 END),
         'fm99999999999999999990.00'
       ) as "Settlement Amount",
       HOST_DATE as "Settlement Date"
FROM   OPR_OPERATION
WHERE  MCC = '6011'
AND    MSG_TYPE ='MSGTPRES'
AND    HOST_DATE >= TRUNC( SYSDATE )
GROUP BY HOST_DATE;

Outputs:

COUNT	Settlement Amount	Settlement Date
1	299.99	2024-05-24 21:23:20
1	200.00	2024-05-24 21:23:19

fiddle