Sum values from one table depending on another table-CodePudding

Let's say that I have two tables:

First table: Accounts table

Accountname	accountCode
Client1	11
Client2	111
Client3	112

Second Table: Account Details

AccountCode	Debit	Credit
11	500	0
11	0	200
111	700	0
112	300	0
112	0	400

I need to make a query that results the next table :

AccountName	TotalDebit	TotalCredit	CumulativeBalance
Client1	500	200	300
Client2	700	0	1000
Client3	300	400	900

I have tried this, but it shows only the AccountCode:

select
    sum(Debit) as TotalDebit,
    sum(Credit) as TotalCredit,
    Account_Code as AC
from AccountDetails as A 
inner join Accounts as B ON A.AccountCode = B.AccountCode
where AccountCode like N'11%'
group by A.AccountCode

CodePudding user response：

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

Fiddle