Sql Recursive Query Adding Amount

Question

This is my table table , what i want is to add the child values to its parent till the top , showing the levels as well meaing , the result return should be , a

Query Used

WITH RecursiveCTE AS (
    -- Anchor member: Select salesmen and their immediate managers
  SELECT coaid,name, parentCOaid
 --,cast(Amount as int)'Amount'
   ,cast(amount as int) amount
   ,1 level
  FROM accounts
  WHERE parentcoaid = 0
  UNION ALL

    ---- Recursive member: Join the CTE with Sales table to get sub-teams
  SELECT s.coaid, s.name
    ,s.ParentCoaid + r.parentcoaid ,cast(s.amount as int) + cast(r.amount as int) 'Amount'
    ,r.level +1
  FROM accounts s
  INNER JOIN RecursiveCTE r ON s.parentcoaid= r.coaid
)

select * from RecursiveCTE
where level = 1

result came this

Answer 1

An interesting task.
See example with some data (the data is slightly different from your example).
First, expand all groups thru all levels.
For example, the graph tree (does not match the test data)

(1)->(2)->(3)          (6)->(7)     (9)
      |                 |
     (4)->(5)          (8) 

is transformed into ( series - each node with all child nodes)

(1)-(2)-(3)-(4)-(5)
(2)-(3)-(4)-(5)
(4)-(5)
(3)
(5)
(6)-(7)-(8)
(7)
(8)
(9)

Then group back by group headers (1),(2),(4),(3),(5),(6) ....
Calculate sum for each node with all child nodes.

with r as (
  select 0 lvl,coaid,parentCoaid,name,coaid as HeadId,amount
  from accounts
  union all
  select lvl+1,a.coaid,a.parentCoaid,a.name,r.HeadId,a.amount
  from r inner join accounts a on a.parentCoaid=r.coaid
)
--  select * from r order by newParentId,lvl
select HeadId as coaid
     ,max(case when lvl=0 then name end) name
     ,max(case when lvl=0 then parentCoaid end) parentCoaid
     ,sum(amount) amount
     ,count(*) cnt
     ,string_agg(coaid,',') within group(order by coaid) path
from r
group by HeadId

Fiddle here

Output

coaid	name	parentCoaid	amount	cnt	path
1	Master 1	0	24000	7	1,3,4,5,6,7,9
2	Master 2	0	3000	2	2,8
3	Master 1-3	1	13000	4	3,4,5,9
4	Master 3-4	3	8000	2	4,9
5	Master 3-5	3	5000	1	5
6	Master 1-6	1	5000	1	6
7	Master 1-6-7	1	6000	1	7
8	Master 2-8	2	3000	1	8
9	Master 4-9	4	3000	1	9
10	Master 10	0	0	1	10

Test data

create table accounts (coaid int, [name] varchar(90),parentCoaid int, amount float);
insert into accounts values
 (1,'Master 1'       ,0,0.0)
,(2,'Master 2'     ,0,0.0)
,(3,'Master 1-3'   ,1,0.0)
,(4,'Master 3-4'   ,3,5000.0)
,(5,'Master 3-5'   ,3,5000.0)
,(6,'Master 1-6'   ,1,5000.0)
,(7,'Master 1-6-7' ,1,6000.0)
,(8,'Master 2-8'   ,2,3000.0)
,(9,'Master 4-9'   ,4,3000.0)
,(10,'Master 10'     ,0,0.0)
;

Graph

( 1 ) -(3)-(4)      (2)      (10)
 |  \    \    \       \
(6) (7)  (5)  (9)     (8)

We can take only nodes with childs and nodes without parents

with r as (
  select 0 lvl,coaid,parentCoaid,name,coaid as HeadId,amount
  from accounts a
  where parentCoaid=0 
    or exists(select coaid from accounts a2 where a2.parentcoaid=a.Coaid)
  union all
  select lvl+1,a.coaid,a.parentCoaid,a.name,r.HeadId,a.amount
  from r inner join accounts a on a.parentCoaid=r.coaid
)
select HeadId as coaid
     ,max(case when lvl=0 then name end) name
     ,max(case when lvl=0 then parentCoaid end) parentCoaid
     ,sum(amount) amount
     ,count(*) cnt
     ,string_agg(coaid,',') within group(order by coaid) path
from r
group by HeadId

coaid	name	parentCoaid	amount	cnt	path
1	Master 1	0	24000	7	1,3,4,5,6,7,9
2	Master 2	0	3000	2	2,8
3	Master 1-3	1	13000	4	3,4,5,9
4	Master 3-4	3	8000	2	4,9
10	Master 10	0	0	1	10

Update2:
Recursive subquery result

lvl	coaid	parentCoaid	name	HeadId	amount
0	1	0	Master 1	1	0
1	3	1	Master 1-3	1	0
1	6	1	Master 1-6	1	5000
1	7	1	Master 1-6-7	1	6000
2	4	3	Master 3-4	1	5000
2	5	3	Master 3-5	1	5000
3	9	4	Master 4-9	1	3000
0	2	0	Master 2	2	0
1	8	2	Master 2-8	2	3000
0	3	1	Master 1-3	3	0
1	4	3	Master 3-4	3	5000
1	5	3	Master 3-5	3	5000
2	9	4	Master 4-9	3	3000
0	4	3	Master 3-4	4	5000
1	9	4	Master 4-9	4	3000
0	10	0	Master 10	10	0