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