Re: Summing hierarchical data SQL

From: vc <boston103_at_hotmail.com>
Date: 5 Oct 2006 08:50:30 -0700
Message-ID: <1160063430.191564.326620@i3g2000cwc.googlegroups.com>

Maxim Demenko wrote:
> 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...

In 10G, it can although I doubt it'd be any faster though:

select
  a.empno,
  a.ename,
  sum(b.Sal)
from
  (select empno, ename, sys_connect_by_path(empno, '/') path    from emp connect by prior empno = mgr start with mgr is null   ) a,
  (select sal, sys_connect_by_path(empno, '/') path    from emp connect by prior empno = mgr start with mgr is null   ) b
where instr(b.path, a.path) = 1
group by a.empno, a.ename

>
> Best regards
>
> Maxim
Received on Thu Oct 05 2006 - 10:50:30 CDT