Finding a recursive sum in an SQL expression

Question

I have two tables A and B: A (id, dep_id) and B (id, amount)

The data in these tables is similar to

 AB id dep_id id amount --- ------- ---- -------- 1 2 1 100 2 3 2 200 3 NULL 3 300 4 NULL 4 400 

The id column in table A contains the id for table B. For a given id in table A, there may be dep_id that contains the id table B.

The requirement is to calculate the sum of the sum of the entries in B and all its dependent records. This needs to be done in a single sql query. I can not use the PL / SQL block for this. Any idea how to do this.

Example:

 sum(id=1) = 100(id=1,dep_id=2) + 200(id=2,dep_id=3) + 300(id=3) = 600 

Answer 1

You can use CONNECT BY ROOT to build a dependency link ( hierarchical query ), then aggregate:

 SQL> SELECT ID, SUM(amount) 2 FROM (SELECT connect_by_root(a.id) ID, b.amount 3 FROM a 4 JOIN b ON a.id = b.id 5 START WITH a.ID = 1 6 CONNECT BY PRIOR a.dep_id = a.ID) 7 GROUP BY ID; ID SUM(AMOUNT) ---------- ----------- 1 600 

Additional solutions are available in a similar, but slightly more complex scheme (for example, id:1 requires 4x id:2 , each of which requires 8x id:3 ) for this SQL check at plsqlchallenge .

Answer 2

As an alternative to Vincents query, you can use:

 select sum(b.amount) from B b where b.id in ( select a.id from A a start with a.id = 1 connect by a.id = prior a.dep_id ); 

SQLFiddle: http://sqlfiddle.com/#!4/d7d1c/5

Answer 3

Another alternative using recursive CTE:

 with recur (id, dep_id, amount)as ( select A.id, A.dep_id, b.amount from A inner join B on A.id = b.id union all select recur.id, a.dep_id, b.amount from recur inner join A on recur.dep_id = a.id inner join B on a.id = b.id ) select id, sum(amount) from recur group by id 

See the fiddle here: http://sqlfiddle.com/#!4/c1c83/2

Answer 4

We can do this with a recursive query. I do not have access to the database, but this is a tough idea. The query results may not be entirely correct, this is in DB2,

 With Ax as ( SELECT ID, DEP_ID FROM A UNION ALL SELECT AX.ID, A.DEP_ID FROM AX, A WHERE AX.DEP_ID = A.ID ) SELECT A.ID, SUM(AMOUNT) FROM AX AS A JOIN B ON A.ID = B.ID 

The result set for 1 may look like in AX like,

 1 2 2 3 1 NULL 2 3 3 NULL 4 NULL 

Answer 5

here the method is a cool trick (from a scheme with different names - you have to adjust it)

 select part_id, new_rec.quantity*sum(math_calc( math,2)) m, unit_of_measure from ( SELECT rownum, level lvl, part_id, quantity, unit_of_measure , connect_by_isleaf || sys_connect_by_path(quantity,'*') math from assembly start with parent_part_id = new_rec.part_id connect by parent_part_id = prior part_id ) p group by part_id, unit_of_measure 

basically - this part

 connect_by_isleaf || sys_connect_by_path(quantity,'*') math 

makes the mathematical equation of summation as a string - then it is analyzed and calculated using this part

 new_rec.quantity*sum(math_calc( math,2)) m