SUM values in a SELF JOIN

[MCCooper]

I have a table of loans where 1 loan can be a parent and can have multiple children. The table looks like this:

LoanID ParentID LoanBalance
1001 0 200000
1002 1001 300000
1003 1001 250000
1004 0 500000

In this example loan ID 1001 is the parent to 1002 and 1003. Loan 1004 has no children.

What I need to return in a query is the total of a "group" of loans on each line like below:

LoanID ParentID LoanBalance TotalLoanBalance
1001 0 200000 750000
1002 1001 300000 750000
1003 1001 250000 750000
1004 0 500000 500000

I have tried some aggregation queries with self joins but have had no luck.

 

[Transcend]

You need something like this, I assume your table is called Loan - if not change Loan to your table name.

select Loan.LoadId, Loan.ParentId,
Balance = CASE isNull(sum(L.balance), 0) WHEN 0 THEN (sum(Loan.balance)) ELSE (sum(L.balance)) END
from Loan
left join Loan L on Loan.parentid = L.loadid
group by Loan.loadId, Loan.parentid, Loan.balance

Transcend