Skip to Main Content
  • Questions
  • sum() at each level in hierarchial query

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, shahzad.

Asked: December 22, 2004 - 8:18 am UTC

Last updated: November 09, 2018 - 10:13 am UTC

Version: 9.2.0.1

Viewed 10K+ times! This question is

You Asked

consider the following query
select level,sys_connect_by_path(last_name,'\') name,salary
from employees
start with last_name='King'
connect by prior employee_id=manager_id

is it possible to get the result
like

name sum_of_all_level
\King 24000
\King\Kochhar 41000 <as king's salary+Kochhar's salary(24000+17000)>
\King\Kochhar\Greenberg 53000 <as 24000+17000+12000 >

that is, addition of salaries of each person


and Tom said...

scott@ORA9IR2> select rpad('*',2*level,'*') || ename name,
2 sal,
3 (select sum(e2.sal)
4 from emp e2
5 start with e2.empno = e1.empno
6 connect by prior e2.mgr = e2.empno) sum_sal
7 from emp e1
8 start with e1.mgr is null
9 connect by prior empno = mgr;

NAME SAL SUM_SAL
------------------------------ ---------- ----------
**KING 5000 5000
****JONES 2975 7975
******SCOTT 3000 10975
********ADAMS 1100 12075
******FORD 3000 10975
********SMITH 800 11775
****BLAKE 2850 7850
******ALLEN 1600 9450
******WARD 1250 9100
******MARTIN 1250 9100
******TURNER 1500 9350
******JAMES 950 8800
****CLARK 2450 7450
******MILLER 1300 8750

14 rows selected.



Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

query-as-column caching

Antonio Vargas, December 22, 2004 - 3:33 pm UTC

Tom, you talked about Oracle keeping some cache for query-as-column results so that they don't recalculate for each row if not needed. Two questions then:

1. How can I see if this cache is being used or not?

2. "should" this query use this cache?


Tom Kyte
December 22, 2004 - 6:40 pm UTC

1) we can generally do that using a user defined function to "count"
2) no -- not this one, the parameter passed into the scalar subquery is e1.empno -- it is "unique", no caching. However, if we modify the query, we can see caching:

scott@ORA9IR2> create or replace function f return number
2 as
3 begin
4 dbms_application_info.set_client_info( userenv('client_info')+1 );
5 return 0;
6 end;
7 /

Function created.

scott@ORA9IR2>
scott@ORA9IR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

scott@ORA9IR2> set autotrace traceonly statistics
scott@ORA9IR2> select rpad('*',2*level,'*') || ename name,
2 sal,
3 (select sum(e2.sal)+f()
4 from emp e2
5 start with e2.empno = e1.empno
6 connect by prior e2.mgr = e2.empno) sum_sal
7 from emp e1
8 start with e1.mgr is null
9 connect by prior empno = mgr;

14 rows selected.


Statistics
----------------------------------------------------------
21 recursive calls
0 db block gets
230 consistent gets
0 physical reads
0 redo size
829 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
106 sorts (memory)
0 sorts (disk)
14 rows processed

scott@ORA9IR2> set autotrace off
scott@ORA9IR2> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
14

That shows 14 rows -- at least 14 subquery invocations


scott@ORA9IR2>
scott@ORA9IR2>
scott@ORA9IR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

scott@ORA9IR2> set autotrace traceonly statistics
scott@ORA9IR2> select rpad('*',2*level,'*') || ename name,
2 sal,
3 (select sum(e2.sal)+f()
4 from emp e2
5 start with e2.empno = e1.empno
6 connect by prior e2.mgr = e2.empno) sum_sal
7 from emp e1
8 connect by prior empno = mgr;

39 rows selected.


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
232 consistent gets
0 physical reads
0 redo size
1555 bytes sent via SQL*Net to client
521 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
97 sorts (memory)
0 sorts (disk)
39 rows processed

scott@ORA9IR2> set autotrace off
scott@ORA9IR2> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
14

39 rows, 14 DISTINCT empno's -- 14 calls -- we cached




connect by level

Lise, November 13, 2007 - 10:55 am UTC

Hi Tom,

Say I had the following on emp:
(empno, freq)=>(1, 10), (2,11), (3,8)
SELECT freq, level
FROM emp
WHERE empno between 1 and 3
CONNECT BY LEVEL <= freq

It works for the first record returned, but then it goes all wrong selecting 1000s of records.
Is there any way round this please?
Tom Kyte
November 16, 2007 - 2:32 pm UTC

that won't work since the first emp record will be connected to the 2nd and 3rd as will they.

your connect by level <= freq - will have emp=1 connecting to emp=2 and emp=3.

and if you try to just have emp=1 connect to emp=1, you get a connect by loop all of a sudden.

ops$tkyte%ORA10GR2> create or replace type tab as table of number
  2  /

Type created.

ops$tkyte%ORA10GR2> select * from emp;

     EMPNO       FREQ
---------- ----------
         1          5
         2          6

ops$tkyte%ORA10GR2> select *
  2    from emp, table( cast( multiset( select level l from dual connect by level <= freq ) as tab) )
  3  /

     EMPNO       FREQ COLUMN_VALUE
---------- ---------- ------------
         1          5            1
         1          5            2
         1          5            3
         1          5            4
         1          5            5
         2          6            1
         2          6            2
         2          6            3
         2          6            4
         2          6            5
         2          6            6

11 rows selected.

Thank you

Lise, November 18, 2007 - 9:19 am UTC

I would never have thought of that!

What about 9i?

Lise, November 19, 2007 - 9:49 am UTC

Sorry I should have said that I am running 9i. Running this in 9i gives the error:
SQL> select *
  2  from emp, table( cast( multiset( select level l from dual connect by level <= freq ) as TAB));
from emp, table( cast( multiset( select level l from dual connect by level <= freq ) as TAB))
                                                     *
ERROR at line 2:
ORA-00600: internal error code, arguments: [12804], [kkdlgtd: bad object type],
[], [], [], [], [], []

Tom Kyte
November 21, 2007 - 12:54 pm UTC

any set with more than FREQ rows will do, all_objects is probably a good candidate

ops$tkyte@ORA920> select *
  2    from emp, table( cast( multiset( select rownum from all_objects where rownum <= freq) as tab) )
  3  /

     EMPNO       FREQ COLUMN_VALUE
---------- ---------- ------------
         1          5            1
         1          5            2
         1          5            3
         1          5            4
         1          5            5
         2          6            1
         2          6            2
         2          6            3
         2          6            4
         2          6            5
         2          6            6

11 rows selected.

Alternate way

Mahendra, November 21, 2007 - 1:05 am UTC

Hi shahzad,

Please find alternate way of getting sum() at each level in hierarchical query

CREATE OR REPLACE FUNCTION f (p_path VARCHAR2)
RETURN NUMBER
IS
l_sum NUMBER := 0;
l_txt VARCHAR2 (4000) := SUBSTR (p_path, 2);
l_val NUMBER := 0;
BEGIN
LOOP

if INSTR (l_txt, '+') > 0 then
l_val := SUBSTR (l_txt, 1, INSTR (l_txt, '+') - 1);
l_txt := SUBSTR (l_txt, INSTR (l_txt, '+') + 1);
else
l_val := l_txt;
l_txt := null;
end if;

l_sum := l_sum + NVL (l_val, 0);

IF l_txt IS NULL
THEN
EXIT;
END IF;

END LOOP;

RETURN (l_sum);
END;
/

select rpad('*',2*level,'*') || empno name,
sal,
f(sys_connect_by_path(sal,'+')) sum_sal
from emp e1
start with e1.mgr is null
connect by prior empno = mgr;

NAME SAL SUM_SAL
--------------- ---------- ----------
**1 10000 10000
****2 20000 30000
******4 40000 70000
******5 50000 80000
****3 30000 40000
******6 60000 100000
******7 70000 110000

sum() at top level in hierarchical query

Janel, May 07, 2008 - 8:41 pm UTC

Hi Tom,

In your very first response, the results give the cumulative value at the bottom of the hierarchy. Is there any way to do this in reverse, so that it builds from the bottom of the hierarchy to the top?

Say, like this?

NAME                                  SAL    SUM_SAL
------------------------------ ---------- ----------
**KING                               5000      12075
****JONES                            2975      7075
******SCOTT                          3000      4100
********ADAMS                        1100      1100


Thanks,
Janel
Tom Kyte
May 08, 2008 - 3:33 am UTC

just change the scalar subquery from:

scott@ORA9IR2> select rpad('*',2*level,'*') || ename name,
  2         sal,
  3             (select sum(e2.sal)
  4                from emp e2
  5                   start with e2.empno = e1.empno
  6             <b>connect by prior e2.mgr = e2.empno</b>) sum_sal
  7    from emp e1
  8   start with e1.mgr is null
  9   connect by prior empno = mgr;


to

scott@ORA9IR2> select rpad('*',2*level,'*') || ename name,
  2         sal,
  3             (select sum(e2.sal)
  4                from emp e2
  5                   start with e2.empno = e1.empno
  6             <b>connect by prior e2.empno = e2.mgr</b>) sum_sal
  7    from emp e1
  8   start with e1.mgr is null
  9   connect by prior empno = mgr;

Wow

A reader, May 08, 2008 - 8:55 am UTC

Tom, it took a second until i realized what you did in the original query. It's just that simple. Amazing. Thanx for posting it.

Beeea-utiful

Janel, May 08, 2008 - 11:50 am UTC

You are the BEST!!!

Thanks,
Janel

Spanning multiple tables

David, March 29, 2012 - 12:44 pm UTC

Love the site Tom. I often end up here as a result of Google searches for some obscure or thorny SQL problem.

I know this is an old thread but it's a goody. Taking the above one step further; suppose your hierarchy is in one table and the data you wish to sum is in another.

Consider a table called prod which has productid and parentid columns which forms a product hierarchy.

Also an income table called inc which has accountno, incometype, productid and amount columns.

Can the above query be rewritten to sum up the amount at every level of the product hierarchy for each accountno, productid, incometype grouping?

Important. There is not necessarily an entry for any given product in the income table so any.

Hope that makes sense.

Only works if nodes are unique

Osian, November 06, 2018 - 12:45 am UTC

Your solution works in the context of an employee tree where each person is likely to appear only once in the org-structure.

However, where the tree represents something more complex such as a build blueprint of (say) some furniture, where the same bolt might appear in multiple parts of the structure, your solution falls over.

Consider the following example:

WITH vehicle_build_structure (part_no, parent_part, qty_per_assembly) AS (
SELECT 'LORRY', '*', null FROM dual UNION -- if it makes things easier, you may substitute "null" for "1"
SELECT 'AXLEA', 'LORRY', 3 FROM dual UNION
SELECT 'BRAKEPAD', 'AXLEA', 2 FROM dual UNION
SELECT 'WHEEL', 'AXLEA', 4 FROM dual UNION
SELECT 'CAR', '*', null FROM dual UNION
SELECT 'AXLEB', 'CAR', 2 FROM dual UNION
SELECT 'BRAKEPAD', 'AXLEB', 2 FROM dual UNION
SELECT 'WHEEL', 'AXLEB', 2 FROM dual
), exploded_structure AS (
SELECT level lev, CONNECT_BY_ROOT s.part_no top_level_part, s.part_no, s.qty_per_assembly,
(
SELECT sum (sp.qty_per_assembly)
FROM vehicle_build_structure sp
START WITH sp.part_no = s.part_no
CONNECT BY prior sp.parent_part = sp.part_no
) sum_of_structure,
(
SELECT exp(sum(ln(sp.qty_per_assembly)))
FROM vehicle_build_structure sp
START WITH sp.part_no = s.part_no
CONNECT BY prior sp.parent_part = sp.part_no
) product_of_structure
FROM vehicle_build_structure s
START WITH s.parent_part = '*'
CONNECT BY prior s.part_no = s.parent_part
)
SELECT e.lev, e.top_level_part, e.part_no, e.qty_per_assembly,
e.sum_of_structure, e.product_of_structure
FROM exploded_structure e

Desired output is for the result set to inform me that it takes 12 wheels to build a lorry. But the actual output gives me 48 (12 wheels to build a lorry * 4 wheels to build a car). What we require is a SQL construct which is intelligent enough to work its way back up the branches of the tree within the context of the current node. Perhaps such a construct does exist, but I haven't been able to find it.
Chris Saxon
November 09, 2018 - 10:13 am UTC

You can do the multiplication in the repeated query of recursive with. This also avoids the need for the exp/ln trick:

with vehicle_build_structure(
  part_no,
  parent_part,
  qty_per_assembly
)as(
  select 'LORRY', '*', null
  from dual
  union -- if it makes things easier, you may substitute "null" for "1"
  select 'AXLEA', 'LORRY', 3
  from dual
  union
  select 'BRAKEPAD', 'AXLEA', 2
  from dual
  union
  select 'WHEEL', 'AXLEA', 4
  from dual
  union
  select 'CAR', '*', null
  from dual
  union
  select 'AXLEB', 'CAR', 2
  from dual
  union
  select 'BRAKEPAD', 'AXLEB', 2
  from dual
  union
  select 'WHEEL', 'AXLEB', 2
  from dual
), tree ( 
  part_no, parent_part, qty_per_assembly, tot_qty, lvl 
) as (
  select part_no, parent_part, qty_per_assembly, 1 tot_qty, 1 lvl
  from   vehicle_build_structure v
  where  parent_part = '*'
  union  all
  select v.part_no, v.parent_part, v.qty_per_assembly, 
         t.tot_qty * v.qty_per_assembly, lvl + 1
  from   tree t
  join   vehicle_build_structure v
  on     t.part_no = v.parent_part
) search depth first by part_no set seq
  select lpad ( ' ', lvl, ' ' ) || part_no,
         parent_part,
         qty_per_assembly,
         tot_qty, lvl, seq
  from   tree;

LPAD('',LVL,'')||PART_NO   PARENT_PART   QTY_PER_ASSEMBLY   TOT_QTY   LVL   SEQ   
 CAR                       *                         <null>         1     1     1 
  AXLEB                    CAR                            2         2     2     2 
   BRAKEPAD                AXLEB                          2         4     3     3 
   WHEEL                   AXLEB                          2         4     3     4 
 LORRY                     *                         <null>         1     1     5 
  AXLEA                    LORRY                          3         3     2     6 
   BRAKEPAD                AXLEA                          2         6     3     7 
   WHEEL                   AXLEA                          4        12     3     8