Re: Hierarchical queries and analytics

From: VC <boston103_at_hotmail.com>
Date: Tue, 16 Mar 2004 04:02:56 GMT
Message-ID: <P%u5c.18948$_w.385528@attbi_s53>

Hello,

"Tom Barnes" <nospam1978_at_yahoo.com> wrote in message news:5af28966.0403150653.22dd72f_at_posting.google.com...
> 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.

Well, it's pretty easy with a correlated subquery -- you do not need analytics:

SQL> select level,

  2         ename,
  3         ( select sum(sal) from emp
  4           start with empno = x.empno
  5           connect by prior empno=mgr
  6         ) sum

  7 from emp x
  8 start with mgr is null
  9 connect by prior empno = mgr
 10 order by 1
 11 /

     LEVEL ENAME                       SUM
---------- -------------------- ----------
         1 KING                      29025
         2 BLAKE                      9400
         2 CLARK                      3750
         2 JONES                     10875
         3 MARTIN                     1250
         3 ALLEN                      1600
         3 FORD                       3800
         3 SCOTT                      4100
         3 MILLER                     1300
         3 TURNER                     1500
         3 JAMES                       950

     LEVEL ENAME                       SUM
---------- -------------------- ----------
         3 WARD                       1250
         4 SMITH                       800
         4 ADAMS                      1100

14 rows selected.

VC Received on Mon Mar 15 2004 - 22:02:56 CST