Forum Stats

  • 3,839,777 Users
  • 2,262,536 Discussions
  • 7,901,054 Comments

Discussions

Cummulative

user11936261
user11936261 Member Posts: 86
edited Jan 21, 2010 1:18AM in SQL & PL/SQL
Hi ,

I have a requirement where i have a table

<pre>
itemno ssitem value
IA1 IB1 5
IB1 IC1 2
IC1 NULL 1
ID1 NULL 3
IF1 IK1 5
IK1 NULL 1

</pre>

I need the cummulative values ,here itemno and ssitem has parent child relation .

For IC1(itemno) chain is like (IC1(itemno)>IC1(ssitem)>IB1(itemno))>IB1(SSITEM)>IA1(itemno)

so we have add all the values and show the value for IC1(itemno) as 8

For ID1(itemno) chain is not there so we will show just the corresponding value i.e 3

For Ik1(itemno) chain is (IK1(ssitem)>IF1(itemno)) so some of two records so 6

<pre>
itemno ssitem value cummulative
IA1 IB1 5 NULL
IB1 IC1 2 NULL
IC1 NULL 1 8
ID1 NULL 3 3
IF1 IK1 5 NULL
IK1 NULL 1 6

</pre>

Please help me out

Thanks

Best Answer

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    Answer ✓
    Like this
    with t
    as
    (
    select 'IA1' itemno, 'IB1' ssitem,5 value from dual union all     
    select 'IB1', 'IC1',2 from dual union all
    select 'IC1', NULL,1 from dual union all
    select 'ID1', NULL,3 from dual union all
    select 'IF1', 'IK1',5 from dual union all
    select 'IK1', NULL,1 from dual 
    )
    select itemno, ssitem, value, decode(ssitem, null,sum(value) over(partition by parent order by lvl desc)) cummulative
      from (
    select itemno, ssitem, value, level lvl, connect_by_root itemno parent
      from t
     start with ssitem is null
    connect by ssitem = prior itemno
           )

Answers

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    Answer ✓
    Like this
    with t
    as
    (
    select 'IA1' itemno, 'IB1' ssitem,5 value from dual union all     
    select 'IB1', 'IC1',2 from dual union all
    select 'IC1', NULL,1 from dual union all
    select 'ID1', NULL,3 from dual union all
    select 'IF1', 'IK1',5 from dual union all
    select 'IK1', NULL,1 from dual 
    )
    select itemno, ssitem, value, decode(ssitem, null,sum(value) over(partition by parent order by lvl desc)) cummulative
      from (
    select itemno, ssitem, value, level lvl, connect_by_root itemno parent
      from t
     start with ssitem is null
    connect by ssitem = prior itemno
           )
  • Twinkle
    Twinkle Member Posts: 740 Silver Badge
    Other way,
    SQL> with tab as
      2  (select 'IA1' itemno,'IB1' ssitem,5 value from dual union all         
      3  select 'IB1','IC1',2 from dual union all
      4  select 'IC1',NULL,1 from dual union all 
      5  select 'ID1',NULL,3 from dual union all
      6  select 'IF1','IK1',5 from dual union all
      7  select 'IK1',NULL,1 from dual)
      8  SELECT it,  (case when max(ssitem) = it or max(ssitem) is null then 'null' else max(ssitem) end
    ) ssitem ,
      9     (case when max(ssitem) = it or max(ssitem) is null then SUM(value) else 0 end) cummulative
     10  FROM (
     11    SELECT CONNECT_BY_ROOT  itemno it,ssitem, value
     12    FROM tab
     13  CONNECT BY PRIOR itemno=ssitem
     14  )
     15  GROUP BY it
     16  order by it;
    
    IT  SSIT CUMMULATIVE
    --- ---- -----------
    IA1 IB1            0
    IB1 IC1            0
    IC1 null           8
    ID1 null           3
    IF1 IK1            0
    IK1 null           6
    
    6 rows selected.
    Twinkle
    Twinkle
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    My solution is similar to Karthick_Arp :D
    col ITEMNO for a10
    col SSITEM for a10
    
    with t as(
    select 'IA1' itemno, 'IB1' ssitem,5 value from dual union all
    select 'IB1', 'IC1',2 from dual union all
    select 'IC1', NULL,1 from dual union all
    select 'ID1', NULL,3 from dual union all
    select 'IF1', 'IK1',5 from dual union all
    select 'IK1', NULL,1 from dual )
    select itemno,ssitem,value,
    case when itemno = root
         then sum(value) over(partition by root) end as cumulative
    from (select connect_by_root itemno as root,itemno,ssitem,value
            from t
          start with SSITEM is null
          connect by prior ITEMNO = SSITEM)
    order by itemno;
    
    ITEMNO  SSITEM  VALUE  cumulative
    ------  ------  -----  ----------
    IA1     IB1         5  null
    IB1     IC1         2  null
    IC1     null        1           8
    ID1     null        3           3
    IF1     IK1         5  null
    IK1     null        1           6
  • bnjutk
    bnjutk Member Posts: 197
    edited Jan 21, 2010 1:18AM
    Hi all,
    Can we do it in pl/sql without using level,connect by,partition.. and automate it for the entire table values.
    Please help me on this.

    Thanks in advance.
This discussion has been closed.