SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE accounts
    (id int, accountname varchar(20), accountcode int)
;
    
INSERT INTO accounts
    (id, accountname, accountcode)
VALUES
    (1, 'client1', 11),
    (2, 'client2', 111),
    (3, 'client3', 112)
;
3 rows affected

CREATE TABLE account_details
    (id int, debit int, credit int, accountcode int)
;
    
INSERT INTO account_details
    (id, debit,credit , accountcode)
VALUES
    (1, 500, null, 11),
    (2, null, 200, 11),
    (3, 700, null, 111),
    (4, 300, null, 112),
    (5, null, 400, 112)

;
5 rows affected

select * from accounts;
select * from account_details


id
accountname
accountcode


1
client1
11

2
client2
111

3
client3
112




id
debit
credit
accountcode


1
500
null
11

2
null
200
11

3
700
null
111

4
300
null
112

5
null
400
112


select   accountname
        ,sum(debit)                                                                      as total_debit
        ,sum(credit)                                                                     as total_credit
        ,sum(coalesce(sum(debit),0)-coalesce(sum(credit),0)) over(order by accountname)  as cumulative_balance 

from     accounts a join account_details ad on ad.accountcode = a.accountcode
group by accountname


accountname
total_debit
total_credit
cumulative_balance


client1
500
200
300

client2
700
null
1000

client3
300
400
900


Warning: Null value is eliminated by an aggregate or other SET operation.

id	debit	credit	accountcode
1	500	null	11
2	null	200	11
3	700	null	111
4	300	null	112
5	null	400	112