Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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