By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DROP TABLE IF EXISTS tenant_category_transaction_view;
CREATE TABLE tenant_category_transaction_view (
id int primary key
, idParentCategory int
, sumSubtotal int
);
INSERT INTO tenant_category_transaction_view VALUES
( 1 ,null ,9800)
,( 4 ,null ,20 )
,( 5 ,4 ,30 )
,( 6 ,5 ,40 )
;
Records: 4 Duplicates: 0 Warnings: 0
with recursive cte (root_id, id, sumSubtotal) as
(
select null
,null
,0
union all
select id
,id
,sumSubtotal
from tenant_category_transaction_view as tctv
union all
select cte.root_id
,tctv.id
,tctv.sumSubtotal
from cte
join tenant_category_transaction_view as tctv
on tctv.idParentCategory <=> cte.id
)
select root_id
,sum(sumSubtotal) as sumSubtotal
from cte
group by root_id
| root_id | sumSubtotal |
|---|---|
| null | 9890 |
| 1 | 9800 |
| 4 | 90 |
| 5 | 70 |
| 6 | 40 |