Search code examples
sqldb2cobol

DB2 SQL query to SUM the column and Subtract column

I have TRAN_AM (Transaction amount) and DB_CRD_ID(Debit Credit ind). I have to SUM the TRAN_AM based on the credit debit indicator in a single query.

Input:

Acct     TRAN_AM     DB_CRD_ID
AAAAAA    10             D 
AAAAAA    10             C
AAAAAA    10             D

My output should be 10, D. Also have to manage negative if Credit is higher.

This query will be used as subquery, As I have to compare this amount with the another table.

Thanks in advance.

Solution 
  • select SUM(TRAN_AM * 
        case DB_CRD_ID 
            when 'D' THEN 1
            when 'C' THEN -1
        END
       ) 
 from TABLE;