1

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);
GMB
  • 216,147
  • 25
  • 84
  • 135
Nvr
  • 171
  • 1
  • 11
  • 1
    You should include any queries that you have tried. – Gordon Linoff Jan 20 '20 at 16:35
  • @GordonLinoff Now i edited my question – Nvr Jan 20 '20 at 16:37
  • A simple aggregation (`SUM()` function with a `GROUP BY` clause) will do. – The Impaler Jan 20 '20 at 16:38
  • @Nvr please can you show us what do you expect to get from the sample data you shown. What will be the result after the query. Cheers! Also, I see prom your past questions: do you know you can accept the answer that is correct ? Do you know you can vote up for the answer that is helpful to you ? – VBoka Jan 20 '20 at 16:40
  • @VBoka i dont know about vote.please guide me about upvoting – Nvr Jan 20 '20 at 16:48
  • @Nvr, VERY NICE. I am sure a lot of people will be happy to see some exstra points from you :) Cheers! – VBoka Jan 20 '20 at 17:02
  • Is there a pre-existing 'beginning balance' that is not accounted for in your sample data? – EdStevens Jan 20 '20 at 17:21

2 Answers2

2

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
GMB
  • 216,147
  • 25
  • 84
  • 135
1

You can just sum up your amounts. The trick is to make your credit (or debit, it's not clear) a negative:

SELECT cust, Sum(CASE WHEN TXN_CODE = 'DEB' THEN -1 ELSE 1 END * amt) as
FROM txn_tbl 
GROUP BY cust
JNevill
  • 46,980
  • 4
  • 38
  • 63