Hi SQL experts!
Some background information:
My database version is: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production.
The table below is the result that I want. How can I achieve it by means of SQL?
| child | parent | status | child_sum | acc_sum |
|---|
| 1 | null | E | 0 | 0 |
| 2 | 1 | A | 10 | 60 |
| 3 | 2 | E | 20 | 20 |
| 4 | 2 | E | 30 | 30 |
Child 1 is the root and children 3 and 4 are leafs. It is only when status is E, that accumulation takes place in last column. Acc_sum for child 2 is: 10+20+30. Child 1 acc sum is 0 because child 2 status is A i e nothing is accumulated from lower level to upper level in tree. Please observe that status <> E can be found anywhere in the structure.
Status could be any character but not null.
Each child has only one parent. My data is a tree.
Here are the scripts for setting up table and data:
create table t (child number, parent number, status varchar2(1), child_sum number, acc_sum number);
insert into t (child, parent, status, child_sum) values (1,null,'E',0); --Root
insert into t (child, parent, status, child_sum) values (2,1,'A',10);
insert into t (child, parent, status, child_sum) values (3,2,'E',20); --Leaf
insert into t (child, parent, status, child_sum) values (4,2,'E',30); --Leaf
Kind regards
/Nicklas