Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE objective ( id, parent_id, label, cost ) AS
SELECT 1, NULL, 'A', 0 FROM DUAL UNION ALL
SELECT 2, 1, 'B', 1 FROM DUAL UNION ALL
SELECT 3, 2, 'C', 3 FROM DUAL UNION ALL
SELECT 4, 1, 'D', 0 FROM DUAL UNION ALL
SELECT 5, 4, 'E', 3 FROM DUAL UNION ALL
SELECT 6, 5, 'F', 7 FROM DUAL UNION ALL
SELECT 7, 4, 'G', 5 FROM DUAL;
7 rows affected

SELECT id,
       parent_id,
       label,
       cost,
       ( SELECT COALESCE(SUM(cost), 0)
         FROM   objective c
         START WITH c.id = o.id
         CONNECT BY PRIOR id = parent_id
       ) AS total_cost
FROM   objective o
START WITH
       parent_id IS NULL
CONNECT BY
       PRIOR id = parent_id
ORDER SIBLINGS BY
       id;


ID
PARENT_ID
LABEL
COST
TOTAL_COST


1
null
A
0
19

2
1
B
1
4

3
2
C
3
3

4
1
D
0
15

5
4
E
3
10

6
5
F
7
7

7
4
G
5
5


SELECT id,
       parent_id,
       label,
       cost,
       ( SELECT COALESCE(SUM(cost), 0)
         FROM   objective c
         WHERE  CONNECT_BY_ISLEAF = 1
         START WITH c.id = o.id
         CONNECT BY PRIOR id = parent_id
       ) AS total_cost
FROM   objective o
START WITH
       parent_id IS NULL
CONNECT BY
       PRIOR id = parent_id
ORDER SIBLINGS BY
       id;


ID
PARENT_ID
LABEL
COST
TOTAL_COST


1
null
A
0
15

2
1
B
1
3

3
2
C
3
3

4
1
D
0
12

5
4
E
3
7

6
5
F
7
7

7
4
G
5
5