As you were advised on your original question
How to get total sum of parent child ?[
^] do this in two passes...
Firstly get the individual amounts for each account, then get the total child amounts for each parent. If you really desperately want this to be a single query then just use multiple CTEs e.g.
;with cte1 AS
(
select Account, ISNULL(SUM([Value]),0) AS [Value]
from #Account A
LEFT OUTER JOIN #AccountLedger L ON L.Account = A.AccountID
group by Account
)
,cte2 AS
(
select Parent, ISNULL(SUM([Value]),0) AS [Value]
from #Account A
LEFT OUTER JOIN #AccountLedger L ON L.Account = A.AccountID
WHERE Parent IS NOT NULL
GROUP BY Parent
)
select AccountID, A.Parent, [Name], ISNULL(cte1.[Value],0) + ISNULL(cte2.[Value],0)
from #account A
LEFT OUTER JOIN cte1 ON A.AccountID = cte1.Account
LEFT OUTER JOIN cte2 ON A.AccountID = cte2.Parent
Points to note:
In my (temp) tables I used the column names and table names from your sample data not the query you posted. It's a really good idea to use the actual column names when giving us sample data. I wasn't inclined to go back and rename things.
Note that
SUM(ISNULL(Accountvalue,0))
is pointless. SUM will ignore NULL values. However, if all the values are NULL then the SUM will return NULL so you need to use
ISNULL(SUM(AccountValue),0)
Although I have used that construct in both cte1 and cte2 I still need to check for NULLs when trying to add the values together
ISNULL(cte1.[Value],0) + ISNULL(cte2.[Value],0)
because I have used LEFT OUTER JOIN - i.e. it is possible for cte1.[Value] to be NULL simply because there are no rows in cte1 for that Id.