Linuxpepe69 Linuxpepe69 - 1 month ago 7
SQL Question

SQL get all children of a parent and add values of children to a parent

Lets say I have a table like this.

ID Parent Value
1 NULL 1000
2 1 1000
3 2 1000
4 2 1000
5 2 1000
6 2 1000
7 2 1000
8 1 1000
9 8 1000
10 8 1000
11 8 1000


I want to add every child value of a given id recursively. The correct output would be.

ID Parent Value
1 NULL 11000
2 1 6000
3 2 1000
4 2 1000
5 2 1000
6 2 1000
7 2 1000
8 1 4000
9 8 1000
10 8 1000
11 8 1000


There is only one "top" parent and it has Parent value of "Null". I'm very new to SQL so any kind of help would be appreciated. I'm using Oracle 11 if that helps.

Answer

Yes, you can do it using the CONNECT_BY_ROOT operator.

Basically, "START WITH" every row, sum up the children for each root, and then group by root. Like this:

with test_data (id, parent, value) as ( 
SELECT 1,   NULL,    1000 FROM DUAL UNION ALL
SELECT 2,   1,       1000 FROM DUAL UNION ALL
SELECT 3,   2,       1000 FROM DUAL UNION ALL
SELECT 4,   2,       1000 FROM DUAL UNION ALL
SELECT 5,   2,       1000 FROM DUAL UNION ALL
SELECT 6,   2,       1000 FROM DUAL UNION ALL
SELECT 7,   2,       1000 FROM DUAL UNION ALL
SELECT 8,   1,       1000 FROM DUAL UNION ALL
SELECT 9,   8,       1000 FROM DUAL UNION ALL
SELECT 10,  8,       1000 FROM DUAL UNION ALL
SELECT 11,  8,       1000 FROM DUAL)
SELECT root_id id, root_parent parent, sum(value) value
FROM ( 
SELECT connect_by_root(id) root_id, connect_by_root(parent) root_parent, value 
FROM test_data td
connect by parent = prior id
-- notice there is no "start with" clause
)
group by root_id, root_parent
order by root_id
Comments