Cumulative Summary in Hierarchical Query using CONNECT_BY_ROOT

Today I will explain how to write hierarchical query, then modify it for getting cumulative summary function of every child using CONNECT_BY_ROOT which is supported within hierarchical queries.

For example I will create hierarchical query for employees and their manager, then display for every manager  how many  employees whose he manages cumulatively.



#1 Build hierarchical Query
Build simple hierarchical  query to retrieve managers and their employees

SELECT LEVEL, EMPNO, LPAD (ENAME, LEVEL * 2 + 3, ' ') ENAME
FROM emp
CONNECT BY PRIOR EMPNO = MGR
START WITH MGR IS NULL;

The result data as below


#2 use CONNECT_BY_ROOT for getting cumulative counter of employees under manager
Display per every manager how many employees whose he manages cumulatively
I will use CONNECT_BY_ROOT in query


SELECT empno, ename, COUNT (CONNECT_BY_ROOT empno) -1 total_employees
FROM emp
CONNECT BY empno = PRIOR mgr
  GROUP BY empno, ename

The result data as below
 
Thanks 
Mahmoud A. El-Sayed

Popular posts from this blog

ADF : Get Current Logged User Name

OAF : Get Current Row in Table

ADF : Working with ViewCriteria