Hierarchical sum of the columns in oracle

user_11650948 0
Viewed 166

I have got one problem as follows:

ID NAME  AMOUNT PARENTID  
1  Adam  1000   0  
2  John  2000   1  
3  Clark 1500   2  
4  Rita  1200   3  
5  jack  1600   3  
6  mark  1800   2  
7  Finn  1500   6  
8  Ryan  1100   6

So the data above is the result of a query with multiple joins and it is a kind of hierarchy or tree something like this:

       1  
       |  
       2  
     /  \   
   3      6  
  / \     / \  
5   4    7   8

and now I need to modify my query so that I get the following result

ID NAME  AMOUNT PARENTID  DownstreamSum   
1  Adam  1000   0         10700     
2  John  2000   1         8700      
3  Clark 1500   2         2800      
4  Rita  1200   3         0         
5  jack  1600   3         0         
6  mark  1800   2         2600      
7  Finn  1500   6         0          
8  Ryan  1100   6         0

So the logic is that the parent should have the sum of all the downstream child nodes in the DownstreamSum column.

for example:

  • id 6 should have the sum of amount of id 7 and 8
  • id 3 should have the sum of amount of id 4 and 5

but id 2 should have the sum of the amount of id 3 and the sum of amount of id 4 and 5

I tried with many possibilities with partition by and group by but I was not able to get the desired output.

recursive-query hierarchical-data oracle sql
3 Answers

Here is the query, please check.

SQL fiddle link

SELECT  Y.*, X.TOTAL_AMOUNT FROM (
    WITH hierarchy (PARENTID, ID) AS (SELECT PARENTID, ID
        FROM Hierarchy_result
        UNION ALL
        SELECT h.PARENTID, bs.ID
        FROM Hierarchy_result  bs, hierarchy  h WHERE bs.PARENTID=h.ID)
        SELECT h.PARENTID AS PARENTID,
        --SUM(FR.AMOUNT)
        /*SUM((CASE WHEN FR.ID=h.PARENTID
            THEN FR.amount
            ELSE 0.0 END))*/
        SUM(FR.amount) TOTAL_AMOUNT
    FROM hierarchy h, Hierarchy_result FR WHERE FR.ID=h.ID
    GROUP BY h.PARENTID 
    ORDER BY h.PARENTID) X, Hierarchy_result Y WHERE X.PARENTID(+) = Y.ID ORDER BY Y.ID;
user_109082740

A typical recursive CTE (available since Oracle 11gR2) will allow you to walk the branches to the leaves. Then a simple SUM() gives you the result you want. For example:

with
n (root_id, current_id, name, amount, parentid) as (
  select id, id, name, amount, parentid from t
 union all
  select n.root_id, t.id, t.name, t.amount, t.parentid
  from n
  join t on t.parentid = n.current_id
)
select t.id, t.name, t.amount, t.parentid,
  sum(n.amount) - t.amount as downstreamsum 
from t
join n on n.root_id = t.id
group by t.id, t.name, t.amount, t.parentid
order by t.id

Result:

ID  NAME   AMOUNT  PARENTID  DOWNSTREAMSUM 
--- ------ ------- --------- ------------- 
 1  Adam     1000         0          10700 
 2  John     2000         1           8700 
 3  Clark    1500         2           2800 
 4  Rita     1200         3              0 
 5  jack     1600         3              0 
 6  mark     1800         2           2600 
 7  Finn     1500         6              0 
 8  Ryan     1100         6              0

See running example at db<>fiddle.

user_64361910

This query also calculates own and child sums and count:

with mcnt (
    id, parentid, name, amount
    ,rnk
    ,level_members_cnt
    ,level_members_sum
    ,own_members_cnt
    ,own_members_sum
    ,child_members_cnt
    ,child_members_sum
    ) as (
   select 
          p.id, p.parentid, p.name, p.amount
        , row_number()over(partition by p.parentid order by p.id) rnk
        , sum((select count(*) from Hierarchy_result m where m.parentid=p.id))over(partition by p.parentid) as level_members_cnt
        , sum((select sum(m.amount) from Hierarchy_result m where m.parentid=p.id))over(partition by p.parentid) as level_members_sum
        , (select count(*) from Hierarchy_result m where m.parentid=p.id) as own_members_cnt
        , (select nvl(sum(m.amount),0) from Hierarchy_result m where m.parentid=p.id) as own_members_sum
        , 0 as child_members_cnt
        , 0 as child_members_sum
   from Hierarchy_result p
   where not exists(select null from Hierarchy_result child where p.id = child.parentid)
   union all
   select p.id, p.parentid, p.name, p.amount
        , row_number()over(partition by p.parentid order by p.id) rnk
        , sum((select count(*) from Hierarchy_result m where m.parentid=p.id))over(partition by p.parentid) level_members_cnt
        , sum((select sum(m.amount) from Hierarchy_result m where m.parentid=p.id))over(partition by p.parentid) level_members_sum
        , (select count(*) from Hierarchy_result m where m.parentid=p.id) as own_members_cnt
        , (select nvl(sum(m.amount),0) from Hierarchy_result m where m.parentid=p.id) as own_members_sum
        , mcnt.level_members_cnt + mcnt.child_members_cnt as child_members_cnt
        , nvl(mcnt.level_members_sum,0) + nvl(mcnt.child_members_sum,0) as child_members_sum
   from mcnt, Hierarchy_result p
   where mcnt.parentid=p.id
     and mcnt.rnk=1
)
select
       id, parentid, name, amount, child_members_cnt, own_members_cnt
      ,child_members_cnt+own_members_cnt as total_cnt
      ,child_members_sum,own_members_sum
      ,child_members_sum+own_members_sum as total_sum
from mcnt
order by id;

Full example with sample data:

with Hierarchy_result ("ID", "NAME", "AMOUNT", "PARENTID") as (
    select 1, 'Adam', 1000, 0 from dual union all
    select 2, 'John', 2000, 1 from dual union all
    select 3, 'Clark', 1500, 2 from dual union all
    select 4, 'Rita', 1200, 3 from dual union all
    select 5, 'jack', 1600, 3 from dual union all
    select 6, 'mark', 1800, 2 from dual union all
    select 7, 'Finn', 1500, 6 from dual union all
    select 8, 'Ryan', 1100, 6 from dual
)
,mcnt (
    id, parentid, name, amount
    ,rnk
    ,level_members_cnt
    ,level_members_sum
    ,own_members_cnt
    ,own_members_sum
    ,child_members_cnt
    ,child_members_sum
    ) as (
   select 
          p.id, p.parentid, p.name, p.amount
        , row_number()over(partition by p.parentid order by p.id) rnk
        , sum((select count(*) from Hierarchy_result m where m.parentid=p.id))over(partition by p.parentid) as level_members_cnt
        , sum((select sum(m.amount) from Hierarchy_result m where m.parentid=p.id))over(partition by p.parentid) as level_members_sum
        , (select count(*) from Hierarchy_result m where m.parentid=p.id) as own_members_cnt
        , (select nvl(sum(m.amount),0) from Hierarchy_result m where m.parentid=p.id) as own_members_sum
        , 0 as child_members_cnt
        , 0 as child_members_sum
   from Hierarchy_result p
   where not exists(select null from Hierarchy_result child where p.id = child.parentid)
   union all
   select p.id, p.parentid, p.name, p.amount
        , row_number()over(partition by p.parentid order by p.id) rnk
        , sum((select count(*) from Hierarchy_result m where m.parentid=p.id))over(partition by p.parentid) level_members_cnt
        , sum((select sum(m.amount) from Hierarchy_result m where m.parentid=p.id))over(partition by p.parentid) level_members_sum
        , (select count(*) from Hierarchy_result m where m.parentid=p.id) as own_members_cnt
        , (select nvl(sum(m.amount),0) from Hierarchy_result m where m.parentid=p.id) as own_members_sum
        , mcnt.level_members_cnt + mcnt.child_members_cnt as child_members_cnt
        , nvl(mcnt.level_members_sum,0) + nvl(mcnt.child_members_sum,0) as child_members_sum
   from mcnt, Hierarchy_result p
   where mcnt.parentid=p.id
     and mcnt.rnk=1
)
select
       id, parentid, name, amount, child_members_cnt, own_members_cnt
      ,child_members_cnt+own_members_cnt as total_cnt
      ,child_members_sum,own_members_sum
      ,child_members_sum+own_members_sum as total_sum
from mcnt
order by id;

Result

 ID PARENTID NAME  CHILD_MEMBERS_SUM OWN_MEMBERS_SUM  TOTAL_SUM CHILD_MEMBERS_CNT OWN_MEMBERS_CNT  TOTAL_CNT
--- -------- ----- ----------------- --------------- ---------- ----------------- --------------- ----------
  1        0 Adam               8700            2000      10700                 6               1          7
  2        1 John               5400            3300       8700                 4               2          6
  3        2 Clark                 0            2800       2800                 0               2          2
  4        3 Rita                  0               0          0                 0               0          0
  5        3 jack                  0               0          0                 0               0          0
  6        2 mark                  0            2600       2600                 0               2          2
  7        6 Finn                  0               0          0                 0               0          0
  8        6 Ryan                  0               0          0                 0               0          0

ps. based on my another solution: Hierarchical Query - Counting records that belongs to Parent and Child Places

user_4291000
Related