Hierarchical queries and analytics

Oracle's CONNECT BY clause is great for writing hierarchical queries. For example, to display employees at the different manager levels (using the standard scott schema):

sql> break on level skip 1
sql> SELECT level, ename, sal FROM emp START WITH mgr IS NULL CONNECT BY PRIOR empno = mgr ORDER BY LEVEL;

     LEVEL ENAME SAL
---------- ---------- ----------

         1 KING 5000

         2 JONES            2975
           CLARK            2450
           BLAKE            2850

         3 SCOTT            3000
           ALLEN            1600
           MARTIN           1250
           MILLER           1300
           JAMES             950
           TURNER           1500
           WARD             1250
           FORD             3000

         4 ADAMS            1100
           SMITH             800

Analytical functions are great for all kinds of aggregate calculations. For example, to get the department totals along with all emps in each department.

sql> break on deptno
sql> SELECT deptno, SUM(sal) OVER (PARTITION BY deptno) Dep_Total, ename, sal FROM emp ORDER BY deptno,ename;

    DEPTNO DEP_TOTAL ENAME SAL
---------- ---------- ---------- ----------

        10       8750 CLARK            2450
                 8750 KING             5000
                 8750 MILLER           1300
        20      10875 ADAMS            1100
                10875 FORD             3000
                10875 JONES            2975
                10875 SCOTT            3000
                10875 SMITH             800
        30       9400 ALLEN            1600
                 9400 BLAKE            2850
                 9400 JAMES             950
                 9400 MARTIN           1250
                 9400 TURNER           1500
                 9400 WARD             1250

But how do I sum up the salary by manager? I'm looking for a query that gives me the following result:

     LEVEL ENAME SAL
---------- ---------- ----------

         1 KING 29025

         2 JONES           10875
           CLARK            3750
           BLAKE            9400

         3 SCOTT            4100
           ALLEN            1600
           MARTIN           1250
           MILLER           1300
           JAMES             950
           TURNER           1500
           WARD             1250
           FORD             3800

         4 ADAMS            1100
           SMITH             800

Thx. Received on Mon Mar 15 2004 - 08:53:50 CST