Sum to Parent Nodes in Hierarchy Queries: CONNECT_BY_ROOT

In one of our Applicaitons, the table DEPT contains a self referncing join (Pigs Ear) as it models our organsational department hierarchy.

For example:

CREATE TABLE DEPT
(
 DEPT_ID NUMBER NOT NULL
,PARENT_ID NUMBER
,DEPT_NAME VARCHAR2 (100) NOT NULL
,EMPLOYEES NUMBER NOT NULL
,CONSTRAINT DEPT_PK PRIMARY KEY (DEPT_ID)
,CONSTRAINT DEPT_FK01 FOREIGN KEY (PARENT_ID) 
 REFERENCES DEPT (DEPT_ID)
);

INSERT INTO DEPT VALUES (1,NULL,'IT', 100);
INSERT INTO DEPT VALUES (2,1,'DEVELOPMENT', 12);
INSERT INTO DEPT VALUES (3,1,'SUPPORT', 15);
INSERT INTO DEPT VALUES (4,1,'TEST', 25);

INSERT INTO DEPT VALUES (5,2,'PL/SQL', 2);
INSERT INTO DEPT VALUES (6,2,'Java', 1);
INSERT INTO DEPT VALUES (7,2,'SQL', 11);
INSERT INTO DEPT VALUES (8,2,'C++', 3);

INSERT INTO DEPT VALUES (9,4,'Functional', 3);
INSERT INTO DEPT VALUES (10,4,'Non Functional', 5);

COMMIT;

A quick tree walk using CONNECT BY PRIOR shows you the Parent / Child relationships between all departments and the number of employees in each department:

SELECT rpad( ' ', 1*level, ' ' ) || dept_name dept_name
      ,employees
  FROM dept
 START WITH parent_id is null
CONNECT BY PRIOR dept_id = parent_id;

DEPT_NAME             EMPLOYEES
-------------------- ----------
 IT                         100
  DEVELOPMENT                12
   PL/SQL                     2
   Java                       1
   SQL                       11
   C++                        3
  SUPPORT                    15
  TEST                       25
   Functional                 3
   Non Functional             5

We had a requirment to work out the total number of employees at each parent level in the Organisational Hierarchy. For example, the report sum up all employees in the parent node and all its children.

Taking the department TEST as an example, the report should sum the figures 25 (employees in the TEST department), 3 and 5 (employees in the Functional / Non Functional child departments) to give a figure of 33.

This can easily be achieved by using CONNECT_BY_ROOT. Straight from the Oracle Documentation:

"CONNECT_BY_ROOT is a unary operator that is valid only in hierarchical queries. When you qualify a column with this operator, Oracle returns the column value using data from the root row. This operator extends the functionality of the CONNECT BY [PRIOR] condition of hierarchical queries.

Restriction on CONNECT_BY_ROOT You cannot specify this operator in the START WITH condition or the CONNECT BY condition."

To meet our requirement, CONNECT_BY_ROOT was utilised as follows:

select dept_name, employees, tot_employees 
from (select 
           employees,
           dept_name,
           level lev,
           sum(employees) over(partition by connect_by_root
                                           (dept_id)
                               ) tot_employees
      from dept
    connect by prior dept_id = parent_id)
    where lev=1;

DEPT_NAME             EMPLOYEES TOT_EMPLOYEES
-------------------- ---------- -------------
IT                          100           177
DEVELOPMENT                  12            29
SUPPORT                      15            15
TEST                         25            33
PL/SQL                        2             2
Java                          1             1
SQL                          11            11
C++                           3             3
Functional                    3             3
Non Functional                5             5