SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE s(Dt date,CID VARCHAR(50),TransactionType VARCHAR(2),Amount INT); INSERT INTO s(Dt,CID,TransactionType,Amount) VALUES ( '2015-01-01','IshaMattoo','CR',18000), ('2015-01-02','IshaMattoo','DB',3000), ('2015-01-03','IshaMattoo','CR',8000), ( '2015-01-04','IshaMattoo','DB',9000), ('2015-01-04','AvtaarKishan','CR',1000), ('2015-01-05','AvtaarKishan','CR',6000),('2015-01-05','AvtaarKishan','DB',5000); SELECT * FROM s order by cid, dt, transactiontype; with cte as (select *, case when transactiontype = 'CR' then coalesce(amount + lag(amount) over (partition by cid order by dt, transactiontype), amount) end as newCR from s) select dt, cid, transactiontype, amount, newCR, case when transactiontype = 'DB' then lag(newCR) over (partition by cid order by dt, transactiontype) end as newDB from cte;