Hierarchical Queries

[grtfercho]

Hi.
I have a list of work_groups or departments. Every work group has a parent and the root of the tree has a value of null for the parent field.

On the other hand I have in my employees table a column that links each employee to a work group.

Making a join like this.

  select level, wkg.work_group_code, lpad(' ', 3*level) ||wkg.work_group_name as wkg_NAME,nvl(ecount.employees,0) emp_count
         from work_groups wkg,
         (select work_group_code,count(*)
                 from employees
                 group by work_group_code) ecount
  start with work_group_code = 'ROOT'
connect by prior work_group_code = parent_work_group;

gives a count of how many employees are assigned to each individual work_group.
LEVEL WORK_GROUP_CODE WKG_NAME EMP_COUNT
1 ROOT Company Name 0
2 INFSERV Information Services 0
3 HRDWR Hardware 5
3 SFTWR Software 10
2 HMNRSCR Human Resources 0
3 BNFTS Benefits 3
3 TRNG Training 2
On the small example above HRDWR and SFTWR are below INFSERV and INSFSERV is below ROOT. Also BNFTS and TRNG are under HMNRSCR and HMNRSCR is under ROOT.

However what I would like to accomplish (Somehow without writting a function) is a full count of all employees under each branch.

Something like this....
LEVEL WORK_GROUP_CODE WKG_NAME EMP_COUNT
1 ROOT Company Name 16
2 INFSERV Information Services 12
3 HRDWR Hardware 3
3 SFTWR Software 9
2 HMNRSCR Human Resources 4
3 BNFTS Benefits 3
3 TRNG Training 1
Any ideas on how to do this???
Any help is greatly appreciated.

Thanks.


grtfercho çB^]\..
"Imagination is more important than Knowledge"A. Einstein

 

[lewisp]

I've not tested this, but you will need something like this:

SELECT level,
       w1.work_group_code,
       lpad(' ', 3*level) || w1.work_group_name as wkg_NAME,
       (SELECT count(*)
        FROM   work_groups w2
        START WITH w2.work_group_code = w1.work_group_code
        CONNECT BY PRIOR w2.work_group_code = w2.parent_work_group) emp_count
FROM   work_groups w1
START WITH w1.work_group_code = 'ROOT'
CONNECT BY PRIOR w1.work_group_code = w1.parent_work_group;