Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Summing hierarchical data SQL

Re: Summing hierarchical data SQL

From: vc <boston103_at_hotmail.com>
Date: 5 Oct 2006 08:57:53 -0700
Message-ID: <1160063872.972376.141210@h48g2000cwc.googlegroups.com>

Michel Cadot wrote:

> "Maxim Demenko" <mdemenko_at_arcor.de> a écrit dans le message de news: 4524c3f1$0$13086$9b4e6d93_at_newsspool2.arcor-online.net...

> | Michel Cadot schrieb:
> | > "Mike C" <michaeljc70_at_hotmail.com> a écrit dans le message de news: 1159993002.453179.263340_at_e3g2000cwe.googlegroups.com...
> | >
> | > Mike C wrote:
> | >> Michel Cadot wrote:
> | >>> "Mike C" <michaeljc70_at_hotmail.com> a écrit dans le message de news: 1159989707.458848.89750_at_c28g2000cwb.googlegroups.com...
> | >>> I've already used the connect by to get the data in this format....
> | >>>
> | >>> -----------------------
> | >>>
> | >>> - Don't top post
> | >>> - So go on, you can easely get the sum over the hierarchy with a connect by.
> | >>>
> | >>> Regards
> | >>> Michel Cadot
> | >> This doesn't really need a CONNECT BY as it only needs to look at 1
> | >> level below. To get the sum for level 1, it only needs to look at
> | >> level 2, etc. If I could "easily get the sum" I would have done it and
> | >> wouldn't have posted to the group!
> | >
> | > I know I can write it something like this, but I was hoping to not have
> | > to repeat code and do it using one of the analytic fucntions if
> | > possible.
> | >
> | > SELECT NAME, level, decode(level,4,t1.frequency,t2.frequency) from
> | > table1 t1,
> | > (select parent_id, sum(frequency) FROM table1 group by parent_id) t2
> | > WHERE t1.id=t2.parent_id(+)
> | >
> | > --------------------------
> | >
> | > Analytic functions are useless here.
> | > From your exemple, you have to walk through the whole hierarchy
> | > and not just one level (else how do you 30 for id 1?).
> | > And connect by is the only way to go through a hierarchy.
> | > I don't think you can get something with your query.
> | >
> | > Regards
> | > Michel Cadot
> | >
> | >
> | The straightforward approach would be probably like
> |

> | SELECT Empno,
> | Ename,
> | Sal + Nvl((SELECT SUM(Sal)
> | FROM Scott.Emp i
> | CONNECT BY PRIOR Empno = Mgr
> | START WITH o.Empno = i.Mgr),
> | 0) Total_SubTree_Sal
> | FROM Scott.Emp o
> |

> | Not sure, about the performance with correlated hierarchical subquery,
> | whereas with not too big volumes of data ( and not too deep hierarchy)
> | should be ok.
> |

> | It might be interesting, if this result can be achieved without
> | correlated subquery however...
> |

> | Best regards
> |

> | Maxim

>
> I don't think so.

select
  a.id,
  a.name,
  parent,
  sum(b.frequency)
from
  (select id, name, parent, sys_connect_by_path(id, '/') path    from t1 connect by prior id = parent start with parent is null   ) a,
  (select frequency, sys_connect_by_path(id, '/') path    from t1 connect by prior id = parent start with parent is null   ) b
where instr(b.path, a.path) = 1
group by a.id, a.name, a.parent
/

ID	NAME	PARENT	SUM(B.FREQUENCY)
1	USA		30
2	CA	1	25
3	LA	2	10
4	SF	2	15
5	IL	1	5
6	Chicago	5	5



> A comment on your query: why starting with o.empno=i.mgr
> and not o.empno=i.empno? This prevent from adding current sal
> and testing if sum(sal) of children is null or not:
>
> SQL> select * from t order by id;
>         ID NAME           PARENT  FREQUENCY
> ---------- ---------- ---------- ----------
>          1 USA
>          2 CA                  1
>          3 LA                  2         10
>          4 SF                  2         15
>          5 IL                  1
>          6 Chicago             5          5
>
> 6 rows selected.
>
> SQL> select id, name, parent,
>   2         ( select sum(frequency)
>   3           from t t2
>   4           connect by prior id = parent
>   5           start with t2.id = t1.id ) frequency
>   6  from t t1
>   7  /
>         ID NAME           PARENT  FREQUENCY
> ---------- ---------- ---------- ----------
>          1 USA                           30
>          2 CA                  1         25
>          3 LA                  2         10
>          4 SF                  2         15
>          5 IL                  1          5
>          6 Chicago             5          5
> 
> 6 rows selected.
> 

> Regards
> Michel Cadot Received on Thu Oct 05 2006 - 10:57:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US