0

This question is in continuation to my previous question on hierarchical query, where Im looking for cumulative sum of values at each level of the hierarchy.

For a schema like the following:

drop table t1 purge;

create table t1(en varchar2(10),bug number, mgr varchar2(10));
insert into t1 values('z',901,'x');
insert into t1 values('z',902,'x');
insert into t1 values('z',903,'x');
insert into t1 values('a',101,'z');
insert into t1 values('a',102,'z');
insert into t1 values('a',103,'z');
insert into t1 values('a',104,'z');
insert into t1 values('b',201,'a');
insert into t1 values('b',202,'a');
insert into t1 values('b',203,'a');
insert into t1 values('c',301,'z');
insert into t1 values('c',302,'z');
insert into t1 values('c',303,'z');
insert into t1 values('d',301,'c');
insert into t1 values('d',302,'c');
commit;

Demo here.

The output I'm expecting is :

MGR  EN   EN_BUG_COUNT  CUMULATIVE_BUG_COUNT    LEVEL
x    null         null                    15        0
x    z               3                    12        1
z    a               4                     7        2
a    b               3                     3        3
z    c               3                     5        2
c    d               2                     2        4

I have spent lot of time and found following links, but the queries they had used is very hard to comprehend:

  1. Oracle hierarchical queries data
  2. How to sum by levels in Hierarchical (tree)? in Oracle
  3. Getting counts at each level of a hierarchical query
markalex
  • 8,623
  • 2
  • 7
  • 32
bprasanna
  • 2,423
  • 3
  • 27
  • 39
  • Your expected output does not make sense as going from `(z, c)` to `(c, d)` goes from level 2 to 4 (not 3). And if you work backwards for the cumulative totals then `(c, d) = 2`, `(z, c) = 3 + (c, d) = 5`, `(a, b) = 3`, `(z, a) = 4 + (a, b) = 7` ... so far so good ... but then `(x, z) = 3 + (z, a) + (z, c) = 15` (not 12). – MT0 Apr 25 '23 at 20:43

1 Answers1

2

Group the bugs and then use a hierarchical query to find the levels and use a correlated hierarchical query to sum the values for descendants:

WITH grouped_bugs (en, en_bug_count, mgr) AS (
  SELECT en,
         COUNT(*),
         mgr
  FROM   t1
  GROUP BY
         en, mgr
)
SELECT mgr,
       en,
       en_bug_count,
       ( SELECT SUM(en_bug_count)
         FROM   grouped_bugs i
         START WITH i.en = g.en
         CONNECT BY PRIOR en = mgr ) AS cumulative_bug_count,
       LEVEL
FROM   grouped_bugs g
START WITH mgr = 'x'
CONNECT BY PRIOR en = mgr
ORDER SIBLINGS BY en;

Which, for the sample data, outputs:

MGR EN EN_BUG_COUNT CUMULATIVE_BUG_COUNT LEVEL
x z 3 15 1
z a 4 7 2
a b 3 3 3
z c 3 5 2
c d 2 2 3

fiddle

MT0
  • 143,790
  • 11
  • 59
  • 117