How to sum only tree leafs in SQL Server 2017?

Question

I need to sum only the leaf nodes of a table organized like the following (fiddle).

create table test_1 (id int, parent_id int, name varchar(10), val int);
insert into test_1 values (1, null, 'ini 1', 1);
insert into test_1 values (2, null, 'ini 2', 2);
insert into test_1 values (3, null, 'ini 3', 3); -- to sum
insert into test_1 values (11, 1, 'lv 1 1', 4); -- to sum
insert into test_1 values (21, 2, 'lv 2 1', 5);
insert into test_1 values (221, 21, 'lv 2 2 1', 6);
insert into test_1 values (222, 21, 'lv 2 2 2', 7); -- to sum
insert into test_1 values (2221, 221, 'lv 2 2 2 1', 8); -- to sum
insert into test_1 values (2222, 221, 'lv 2 2 2 2', 9); -- to sum
insert into test_1 values (2223, 221, 'lv 2 2 2 3', 0); -- to sum

ini 1                 (1)
 |- lv 1 1            (4) <-
ini 2                 (2)
 |- lv 2 1            (5)
    |- lv 2 2 1       (6) <-
    |- lv 2 2 2       (7)
        |- lv 2 2 2 1 (8) <-
        |- lv 2 2 2 2 (9) <-
        |- lv 2 2 2 3 (0) <-
ini 3                 (3) <-

4 + 6 + 8 + 9 + 0 + 3 = 30

The actual table is massive though. What would be a fast way to get the sum of all leaf nodes as exemplified?

Answer 1

It seems the correct sum is 31 = (3 + 4 + 7 + 8 + 9 + 0)

select sum(val)
from test_1 t
where not exists (select *
                  from test_1 tt
                  where t.id=tt.parent_id);

31

link