How to get total sum of parent child ?

Question

0.00/5 (No votes)

See more:

we have two tables. and using these table can we get this result.Using single select query.

<pre>Account Table
AccountID	Name	Parent
1	Header 100	NULL
2	Header 110	1
3	Child 111	2
4	Child 112	2
5	Child 113	2
6	Child 120	1
7	Header 130	1
8	Child 131	7
9	Child 132	7
10	Header 200	NULL
11	Header 210	10
12	Child 221	10
13	Child 220	10
14	Header 300	NULL
15	Child 310	14

AccountLedger Table 
Account	Value
3	50.00
3	10.00
3	50.00
3	80.00
3	40.00
4	20.00
4	10.00
4	100.00
5	80.00
5	90.00
5	60.00
6	100.00
8	40.00
8	70.00
8	10.00
8	40.00
9	20.00
12	60.00
12	30.00
12	70.00
13	10.00
13	100.00
15	30.00
15	90.00
15	40.00

OUTPUT NEEDED :-
AccountId	Parent	Name	value
1	NULL	Header 100	870.00
2	1	Header 110	590.00
3	2	Child 111	230.00
4	2	Child 112	130.00
5	2	Child 113	230.00
6	1	Child 120	100.00
7	1	Header 130	180.00
8	7	Child 131	160.00
9	7	Child 132	20.00
10	NULL	Header 200	270.00
11	10	Header 210	0.00
12	10	Child 221	160.00
13	10	Child 220	110.00
14	NULL	Header 300	160.00
15	14	Child 310	160.00

What I have tried:

;With CTE1
AS
(
Select AccountId,AccountName, ParentId,null as Accountvalue From AccountList
Union All
Select A.AccountId, A.AccountName, A.ParentId,SUM(ISNULL(Accountvalue,0)) From AccountList A
Inner Join Accountledger T On A.AccountId = T.Account GROUP BY AccountId,AccountName,ParentID 
)
Select Accountid, AccountName ,ParentId,SUM(ISNULL(Accountvalue,0)) as AccountValue 
From CTE1 
Group By Accountid,AccountName, ParentId
ORDER BY AccountId--option (maxrecursion 0)

Posted 9-Apr-19 1:58am

Member 14156756

Updated 2-Mar-22 1:28am

Add a Solution

Comments

CHill60 9-Apr-19 9:19am

I don't see how the value for AccountId 1 can possibly be 870.00 … only accounts 2, 6, 7 have that as a parent and only Account 6 has a ledger (100.00)
What is wrong with the solution that was posted on your first (identical) question?

Member 14156756 9-Apr-19 9:39am

parent sum is not calculate in my result.

CHill60 9-Apr-19 9:53am

See my solution below - which based Gerry's @salty06 from your original question

Member 14156756 10-Apr-19 0:13am

Still we can not get Main Parent total from this result.

We get Out put:-
1 NULL Header 100 100
2 1 Header 110 590
3 2 Child 111 230
4 2 Child 112 130
5 2 Child 113 230
6 1 Child 120 100
7 1 Header 130 180
8 7 Child 131 160
9 7 Child 132 20
10 NULL Header 200 270
11 10 Header 210 0
12 10 Child 221 160
13 10 Child 220 110
14 NULL Header 300 160
15 14 Child 310 0

Where Main Parent 'Header 100' Total is 870. But we Got 100.

Member 14156756 10-Apr-19 4:51am

Can you help me to get sum of parent and their child

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2019-04-09T03:42:00

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.

SQL

;with cte1 AS
(
	-- sum of individual amounts for each account
	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
(
	-- Sum of all child amounts for each parent
	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.

Member 15552739 · Answer 2 · 2022-03-02T01:28:00

account table as account_tran1
account ledger table as account_tran2

SQL

;with C as  
(  
  select T.AccountID,  
         T.Name,  
         T.AccountID as RootID  
  from account_tran1 T  
  union all  
  select T.AccountID, 
         T.Name,  
         C.RootID  
  from account_tran1 T  
    inner join C  
      on T.Parent = C.AccountID
)  
  
select t.AccountID,t.Name,t.Parent,
       S.TransectionValue  
from account_tran1 T
LEFT JOIN (  
             select RootID,  
                    sum(t.[Transection value]) as TransectionValue  
             from C  
			 Inner JOIN account_tran2 t on t.Accountid=c.AccountID
             group by RootID  
             ) as S  
    on T.AccountID = S.RootID  
order by T.AccountID

How to get total sum of parent child ?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

How to get total sum of parent child ?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us