Aggregating nested set models with field constraints
These are the steps to obtain the desired result:

A cross self-join to obtain every single combination of primary keys possible (this squares the number of records);
Apply the nested set filter (commented below) to remove all combinations of nodes that are not related (e.g., pk 1 and 15);
Assert that only combinations of the same nature are being fetched and aggregated, by equalling the idx from both node and crosst;
Since we are aggregating from crosst.idx, that means it will sum the &quot;balance&quot; for all nodes, including those that are negative (and thus orphans); the third filter ensures we are only taking the total balance starting from original positive parents.

SELECT
    crosst.pk AS prnt_pk,
    crosst.idx,
    SUM(node.value) AS aggr
FROM
    nsm_table AS node,
    nsm_table AS crosst
WHERE 1=1
    AND node.lft BETWEEN crosst.lft AND crosst.rgt --NSM filter for containers
    AND node.idx = crosst.idx
    AND crosst.value &gt; 0
GROUP BY crosst.pk, crosst.idx
HAVING SUM(node.value) &gt;= 0

And the output is exactly as the desired one:
prnt_pk      idx            aggr
1           2783        0.000000
3            131       78.547946
5              1        0.013421
7            873    10791.237266
9           1697      128.626280
11          1168        6.949175
13          1758      853.502787
15          3794     3172.428304
16          2783        0.000000
18             1        0.039938
21          2783     3012.050000