how to calculate the amount?

In postgresql using query

WITH RECURSIVE rel_rec AS (
    SELECT
            1 AS depth,
            *,
            ARRAY[lvl] AS child_path
        FROM types_of_work
        WHERE parent_id IS NULL AND project_id = 14

        UNION ALL
        SELECT
            nlevel(r.path) + 1,
            n.*,
            r.child_path || n.lvl
        FROM rel_rec AS r
        JOIN types_of_work AS n ON n.parent_id = r.tow_id
        WHERE r.project_id = 14
)
SELECT
    t0.tow_id,
    t0.project_id,
    t0.path,
    t0.child_path,
    t0.lvl,
    t0.parent_id,
    t0.depth,
    t2.tow_cost
FROM rel_rec t0
LEFT JOIN (
    SELECT
        tow_id,
        tow_cost,
        tow_cost_percent,
        tow_date_start,
        tow_date_finish
    FROM tows_contract
    WHERE contract_id = 10
) AS t2 ON t0.tow_id = t2.tow_id
ORDER BY t0.child_path, t0.lvl;

we get a table

tow_id integer	path ltree	child_path smallint[]	lvl smallint	parent_id integer	depth integer	cost numeric	CUMULATIVE_COST
39	root	{1}	1	null	1		∑ 90
40	root.39	{1,2}	2	39	2	10
131	root.39	{1,3}	3	39	2	10
41	root.39	{1,4}	4	39	2	10
46	root.39	{1,5}	5	39	2		∑ 60
47	root.39.46	{1,5,6}	6	46	3		∑ 30
48	root.39.46.47	{1,5,6,7}	7	47	4		∑ 10
134	root.39.46.47.48	{1,5,6,7,8}	8	48	5	10
49	root.39.46.47	{1,5,6,9}	9	47	4	10
125	root.39.46.47	{1,5,6,10}	10	47	4	10
132	root.39.46	{1,5,11}	11	46	3		∑ 20
133	root.39.46.132	{1,5,11,12}	12	132	4	10
135	root.39.46.132	{1,5,11,13}	13	132	4	10
136	root.39.46	{1,5,14}	14	46	3		∑ 10
657	root.39.46.136	{1,5,14,15}	15	136	4	10
142	root	{16}	16	null	1		∑ 30
143	root.142	{16,17}	17	142	2		∑ 30
178	root.142.143	{16,17,18}	18	143	3	10
146	root.142.143	{16,17,19}	19	143	3	10
147	root.142.143	{16,17,20}	20	143	3	10
42	root	{21}	21	null	1		∑ 10
43	root.39.42	{21,22}	22	42	2		∑ 10
45	root.39.42.43	{21,22,23}	23	43	3		∑ 10
671	root.39.42.43.45	{21,22,23,24}	24	45	4		∑ 10
672	root.39.42.43.45.671	{21,22,23,24,25}	25	671	5	10

How to add the calculated sum of the costs of all nested children to this table? An example of this sum is the CUMULATIVE_COST column. The table presented is a tree system. The depth column shows the nesting level The parent_id column shows the tow_id of the parent entity

minimal, reproducible example:

CREATE TABLE types_of_work (
    tow_id integer,
    path ltree,
    lvl integer,
    project_id integer,
    parent_id  integer
);
CREATE TABLE tows_contract (
    tow_id integer,
    cost integer,
    contract_id integer
);

INSERT INTO types_of_work (tow_id, path, project_id, lvl, parent_id)
    VALUES (39, 'root', 14, 1, null),
    VALUES (40, 'root.39', 14, 2, 39),
    VALUES (131, 'root.39', 14, 3, 39),
    VALUES (41, 'root.39', 14, 5, 39),
    VALUES (46, 'root.39', 14, 5, 39),
    VALUES (47, 'root.39.46', 14, 6, 46),
    VALUES (48, 'root.39.46.47', 14, 7, 47),
    VALUES (134, 'root.39.46.47.48',  14, 8, 48),
    VALUES (49, 'root.39.46.47', 14, 9, 47),
    VALUES (125, 'root.39.46.47', 14, 10, 47),
    VALUES (132, 'root.39.46', 14, 11, 46),
    VALUES (133, 'root.39.46.132', 14, 12, 132),
    VALUES (135, 'root.39.46.132', 14, 13, 132),
    VALUES (136, 'root.39.46', 14, 14, 46),
    VALUES (657, 'root.39.46.132', 14, 15, 136),
    VALUES (142, 'root', 14, 16, null),
    VALUES (143, 'root.142', 14, 17, 142),
    VALUES (178, 'root.142.143', 14, 18, 143),
    VALUES (146, 'root.142.143', 14, 19, 143),
    VALUES (147, 'root.142.143', 14, 20, 143),
    VALUES (42, 'root', 14, 21, null),
    VALUES (43, 'root.42', 14, 22, 42),
    VALUES (45, 'root.42.43',  14, 23, 43),
    VALUES (671, 'root.42.43.45', 14, 24, 45),
    VALUES (672, 'root.42.43.45.671', 14, 25, 671)
;
INSERT INTO tows_contract (tow_id, cost, contract_id)
    VALUES (40, 10, 10),
    VALUES (131, 10, 10),
    VALUES (41, 10, 10),
    VALUES (134, 10, 10),
    VALUES (49, 10, 10),
    VALUES (125, 10, 10),
    VALUES (133, 10, 10),
    VALUES (135, 10, 10),
    VALUES (657, 10, 10),
    VALUES (178, 10, 10),
    VALUES (146, 10, 10),
    VALUES (147, 10, 10),
    VALUES (672, 10, 10)
;

as an option, you need to sum up the cost of all the following lines, while these lines have a greater depth value than the current one

Solution

Join the table back to itself with the LTREE ancestor operator, <@

with costs as (
  select t.*, c.cost, c.contract_id, t.path||t.tow_id::text as fullpath
    from types_of_work t
         left join tows_contract c on c.tow_id = t.tow_id
)
select l.*, sum(r.cost) as cumulative_cost
  from costs l
       join costs r on r.fullpath <@ l.fullpath
 group by l.tow_id, l.path, l.fullpath, l.project_id, l.lvl, l.parent_id, 
          l.cost, l.contract_id
 order by l.path;

Working fiddle