Sum values from one table depending on another table

Let’s say that I have two tables:

First table: Accounts table

Accountname accountCode
Client1 11
Client2 111
Client3 112

Second Table: Account Details

enter image description here

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

enter image description here

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

    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

>Solution :

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


Leave a Reply