Re: Summing hierarchical data SQL

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(+) Received on Wed Oct 04 2006 - 15:16:42 CDT