Parent and Part Relationship Cost Calculation using SQL-CodePudding

I have a Child and Parent relationship table with cost. I need to roll up the child part costs into parent. Attached is the image for the table. Reqd. column shows the cost rollup I need. But I am not able to do it using sql queries. Also, recursive queries won't help since total line items are more than 100k. Any suggestion or ideas will be very helpful.

Parent Child relationship

CodePudding user response：

I don't understand you are saying you can't use CTE & SQL query. So what technical base do you want to rely on?

By SQL it is totally doable with the functions of aggregate & joins

EDIT:

Without your schema, try to adapt this query for your table :

SELECT j.ID, j.Parent_ID, SUM(t.Cost)
FROM MyTableCost as t
INNER join MyTableCost AS j on j.ID = t.Parent_ID
GROUP BY j.ID, j.Parent_ID;

CodePudding user response：

One self-join seems to be enough.
But I don't see the logic for rolling up id 4.

select t0.parent_id, t0.part_id, t0.cost
, coalesce(sum(t1.cost), t0.cost) as reqd_cost
from your_weird_hierarchy_table t0
left join your_weird_hierarchy_table t1
  on t1.parent_id = t0.part_id
group by t0.parent_id, t0.part_id, t0.cost;

parent_id | part_id |   cost | reqd_cost
--------: | ------: | -----: | --------:
        1 |       2 | 1.0000 |    4.0000
        1 |       3 | 2.0000 |    9.0000
        1 |       4 | 1.0000 |    1.0000
        2 |       3 | 3.0000 |    9.0000
        2 |       4 | 1.0000 |    1.0000
        3 |       4 | 5.0000 |    5.0000
        3 |       5 | 3.0000 |    3.0000
        3 |       6 | 1.0000 |    1.0000

db<>fiddle here