Re: Summing hierarchical data SQL

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. Received on Thu Oct 05 2006 - 16:17:03 CDT