CODEWITHSUNDEEP
sql
steven fuchs
steven fuchs

Reputation: 29

conditional sorting a column dependent on another column value SQL

I work for a bank and I am trying to write a query for some of our transactions and each transaction is input as the transaction amount (TXN_AMOUNT_BASE) and later identified in another column if it is a Credit or Debit (CREDIT_DEBIT_CODE).

Long term goal is to get a query that tells you the ratio of Credits to Debits for each customer.

Immediate goal is to simply separate the amounts into a Credit column or Debit column. This is what I have so far:

SELECT 
    PRIMARY_CUST_SRCE_REF_ID,
    TXN_AMOUNT_BASE,
FROM 
    IDP_INTERFACE.V_L3_HPT_TRANSACTIONS
WHERE 
    CREDIT_DEBIT_CODE = 'C' AS Credit
    AND CREDIT_DEBIT_CODE = 'D' AS Debit
GROUP BY 
    (PRIMARY_CUST_SRCE_REF_ID, TXN_AMOUNT_BASE, CREDIT_DEBIT_CODE)

I know I am wrong on the WHERE C_D_CODE AS segment but I don't know where to start in correcting it.

Thanks

Upvotes: 2

Views: 104

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270663

I think you want conditional aggregation:

SELECT PRIMARY_CUST_SRCE_REF_ID,
       SUM(CASE WHEN CREDIT_DEBIT_CODE = 'C' THEN TXN_AMOUNT_BASE ELSE 0 END) AS Credit,
       SUM(CASE WHEN CREDIT_DEBIT_CODE = 'D' THEN TXN_AMOUNT_BASE ELSE 0 END) AS Debit
FROM IDP_INTERFACE.V_L3_HPT_TRANSACTIONS
WHERE CREDIT_DEBIT_CODE IN ('C', 'D')
GROUP BY PRIMARY_CUST_SRCE_REF_ID;

This gets you the sum of credits and debits for each customer ref. If you want the ratio then divide the values.

Upvotes: 1

Related Questions