Forum Stats

  • 3,836,825 Users
  • 2,262,197 Discussions
  • 7,900,120 Comments

Discussions

Categories

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
· Share on TwitterShare on Facebook

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
       )
    · Share on TwitterShare on Facebook

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
       )
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • 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.
    · Share on TwitterShare on Facebook
This discussion has been closed.