How to fix Oracle hierarchical query returning duplicate records?

While trying hierarchical query in Oracle using connect by it returns duplicate child records for each parent record (based on column association).

For example:

drop table t1 purge;

create table t1(en varchar2(10),bug number, mgr varchar2(10));

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');
commit;

select en, bug, level from t1
start with mgr='z'
connect by prior en=mgr;

is returning following result:

EN  BUG LEVEL
a   101 1
b   201 2
b   203 2
b   202 2
a   102 1
b   201 2
b   203 2
b   202 2
a   103 1
b   201 2
b   203 2
b   202 2
a   104 1
b   201 2
b   203 2
b   202 2
c   301 1
c   302 1
c   303 1

But, what I was expecting was hierarchical display of each unique bug numbers based on the hierarchy defined in columns en and mgr.

The output I'm expecting is (based on the mgr column):

EN  BUG LEVEL
a   101 1
a   102 1
a   103 1
c   301 1
c   302 1
c   303 1
b   201 2
b   203 2
b   202 2

It would be a great help if anyone point out what am I missing here.

Update Requirement to group by number of bugs by each employee. Expected output:

EN  BUG_COUNT   LEVEL
a   4           1
c   3           1
b   3           2

Update 2 If we have to group by the count at each employee and manager level, what would be the query? The expected output is:

MGR  EN   EN_BUG_COUNT  CUMULATIVE_BUG_COUNT    LEVEL
z    null         null                    10        0
z    a               4                     7        1
a    b               3                     3        2
z    c               3                     3        1

Solution

You'll need to create tree using distinct values of en and mgr. And only then join bug codes to it.

It can be done with the following query:

with dist_pairs as (
  select distinct en, mgr
  from t1
),
tree as(
  select en, level lvl
  from dist_pairs
  start with mgr='z'
  connect by prior en=mgr
)
select tree.lvl, t1.*
from t1
join tree on (t1.en=tree.en)

Demo here.

Aggregated output is even easier:

select en, BUG_COUNT, level
from (
  select en, mgr, count(*) BUG_COUNT
  from t1
  group by en, mgr) dist_pairs
start with mgr='z'
connect by prior en=mgr

Demo here.