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
Advertisement
Answer
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