How to calculate balance from credit and debit?

Question

How to get balance amount based on credit CRD and debit DEB for each customer cust from following txn_tbl table

SQL> SELECT * FROM txn_tbl;

CUS        AMT TXN_CDE
--- ---------- ----------
A          500 CRD
B          400 CRD
A          350 CRD
C          235 DEB
C          800 CRD
B          132 DEB
D          673 CRD

This is the query that i had tried

SELECT cust, LAG(amt,1,0) OVER (PARTITION BY cust ORDER BY cust) "Bal"
 FROM 
(SELECT cust, SUM(amt)amt
 FROM txn_tbl
 GROUP BY cust, txn_cde
 ORDER BY 2);

Answer 1

If you want the running balance, you can do a window sum with conditional logic:

select 
    t.*,
    sum(case when txn_cde = 'CRD' then amt else -amt end) 
        over(partition by cus order by id) running_balance
from mytable

For this to work, you need a column that can be used to order the records; I assumed id.

On the other hand, if you want the overall balance per customer (so just one record per customer in the resultset), you can use the same logic with aggregation (and you don't need an ordering colum):

select cus, sum(case when txn_cde = 'CRD' then amt else -amt end) balance
from mytable
group by cus