CODEWITHSUNDEEP
oracle-databaseplsql
Ömer Faruk
Ömer Faruk

Reputation: 111

PL Sql Hierarchical Branch Sum

I have this table. only detail table get value ; I want to get sum of branches .

ACCOUNT         Value             
---------- ------------- 
100                           
100-01                    
100-01-01                 
100-01-01-001     7       
100-01-01-006     6       
100-01-01-271     5       
100-02            
100-02-01-001     1       
100-02-01-006     2       
100-02-01-271     3

Like 

ACCOUNT         Value             
---------- ------------- 
100               24 -- (sum of 100%)            
100-01            18 --  (sum of 100-01%)        
100-01-01         18 --(sum of 100-01-01%)        
100-01-01-001      7       
100-01-01-006      6       
100-01-01-271      5       
100-02             6 -- (sum of 100-02%)
100-02-01-001      1       
100-02-01-006      2       
100-02-01-271      3

I tried sum over by and and tried sum(value) where account like account||'-%' but can not ended up

Upvotes: 0

Views: 82

Answers (1)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

Use subquery:

select account, (select sum(value) from t where account like a.account||'%') account from t a

Example:

with t(account, value) as (
  select '100',           null from dual union all
  select '100-01',        null from dual union all
  select '100-01-01',     null from dual union all
  select '100-01-01-001', 7    from dual union all
  select '100-01-01-006', 6    from dual union all
  select '100-01-01-271', 5    from dual union all
  select '100-02',        null from dual union all            
  select '100-02-01-001', 1    from dual union all
  select '100-02-01-006', 2    from dual union all
  select '100-02-01-271', 3    from dual )
select account, (select sum(value) from t 
  where account like a.account||'%') account from t a

Upvotes: 5

Related Questions