How to get the cumulative sum of children up its parents?

Question

So I have written a query to get the cumulative sum of children but I think partition sum has error as its totalling for the parent that is not part of the children.

My fiddle is http://sqlfiddle.com/#!15/88828/1 I have dont a running total but thats wrong. I want the siblings total to a child and child total back to its parent.So basically cumulative total of a child up the tree.

expected output

    parent_child_tree   id  name    dimensionvalueid    level   order_sequence  volume  cummulative_total
  A1                     1  A1        (null)              0       1              20     840
-----A1:1                2  A1:1       1                  1       1_1           (null)  820
----------A1:1:1         3  A1:1:1     2                  2       1_1_2          20     820
-----------A1:1:1:1      4  A1:1:1:1   3                  3       1_1_2_3        300    800
-----------A1:1:1:2      5  A1:1:1:2.  3                  3       1_1_2_3        500    500
B1                       6  B1        (null)              0       6              200    300
-----B1:2                8  B1:2       6                  1       6_6           (null)  null
-----B1:1                7  B1:1       6                  1       6_6           (null)  100
----------B1:2:1         9  B1:2:1     8                  2       6_6_8         100     100

Answer 1

To get totals for tree nodes you need to generate hierarchy tree for every node in a subquery like this

SELECT
    d.*,
    v.volume,
    (
        WITH RECURSIVE cte AS (
                SELECT 
                    dd.id AS branch_id,
                    dd.id
                FROM dimensionvalue dd
                WHERE dd.id = d.id
                UNION ALL
                SELECT
                    cte.branch_id,
                    dd.id
                FROM dimensionvalue dd
                JOIN cte ON dd.dimensionvalueid = cte.id
        )
        SELECT SUM(v.volume)
        FROM cte
        JOIN valuation v ON v.dimensionvalueid = cte.id
        GROUP BY cte.branch_id
    ) AS totals
FROM dimensionvalue d
LEFT JOIN valuation v ON v.dimensionvalueid = d.id
ORDER BY d.name;

If you really need all those "decoration" columns that you generate in your query for each tree node than you can combine your recursive CTE hierarchy with subquery for totals calculation like this

WITH RECURSIVE hierarchy AS (
    SELECT
    d.id,
    d.name,
    d.dimensionvalueid,
    0 AS level,
        CAST(d.id AS varchar(50)) AS order_sequence
    FROM dimensionvalue d
    WHERE d.dimensionvalueid IS NULL
    UNION ALL
    SELECT
    e.id,
    e.name,
    e.dimensionvalueid,
    hierarchy.level + 1 AS level,
    CAST(hierarchy.order_sequence || '_' || CAST(hierarchy.id AS VARCHAR(50)) AS VARCHAR(50)) AS order_sequence
    FROM hierarchy
    JOIN dimensionvalue e ON e.dimensionvalueid = hierarchy.id
)
SELECT
    RIGHT('-----------', h.level * 5) || h.name || ' ' AS parent_child_tree, 
    h.*,
    v.volume,
    (
        WITH RECURSIVE cte AS (
                SELECT 
                    dd.id AS branch_id,
                    dd.id
                FROM dimensionvalue dd
                WHERE dd.id = h.id
                UNION ALL
                SELECT
                    cte.branch_id,
                    dd.id
                FROM dimensionvalue dd
                JOIN cte ON dd.dimensionvalueid = cte.id
        )
        SELECT SUM(v.volume)
        FROM cte
        JOIN valuation v ON v.dimensionvalueid = cte.id
        GROUP BY cte.branch_id
    ) AS totals
FROM hierarchy h
LEFT JOIN valuation v ON v.dimensionvalueid = h.id
ORDER BY h.name

You can check a working demo here