Re: Summing hierarchical data SQL

"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 Received on Wed Oct 04 2006 - 15:48:37 CDT