Re: Summing hierarchical data SQL

Mike C wrote:
> Mike C wrote:
> > If I have a table or query that returns hierarchical data, how can I
> > sum it? There are counts only at the lowest level and I want to roll
> > it up to the parent levels. Here is an example:
> >
> >
> > Level ID Name Parent Frequency
> > 1 1 USA NULL
> > 2 2 CA 1
> > 3 3 LA 2 10
> > 3 4 SF 2 15
> > 2 5 IL 1
> > 3 6 Chicago 5 5
> >
> >
> > I want to get this:
> >
> > Level ID Name Parent Frequency
> > 1 1 USA NULL 30
> > 2 2 CA 1 25
> > 3 3 LA 2 10
> > 3 4 SF 2 15
> > 2 5 IL 1 5
> > 3 6 Chicago 5 5
>
> I know have a related problem. I need to take the frequency and
> calculate the percentage. That would look like this using the same
> example.
> > Level ID Name Parent Frequency PCT
> > 1 1 USA NULL 30 100
> > 2 2 CA 1 25 83 (25/30)
> > 3 3 LA 2 10 40 (10/25)
> > 3 4 SF 2 15 60 (15/25)
> > 2 5 IL 1 5 100
> > 3 6 Chicago 5 5 100
>
> I tried using the same technique with no luck. It is calculating them
> using ALL at that level rather than just for under that branch of the
> parent.

Okay, I did figure out myself....

select *,
ratio_to_report(frequency) over (partition by level||NVL(parent_id,id) ) * 100 percentage from tbl Received on Thu Oct 05 2006 - 17:05:37 CDT