Finding recursive sum in SQL statement

Question

I've two tables A and B like: A (id, dep_id) and B (id, amount)

The data in those tables are like this

A                 B

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 holds id for table B. For a given id in table A, there might be a dep_id which holds id of table B.

The requirement is to calculate the sum of amount of an entry in B and all of its dependent entries. This has to be done in one single sql query. I can't use PL/SQL block for that. Any idea how to do that.

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 link of dependency (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 on a similar but slightly more complex schema (for example id:1 needs 4xid:2, which needs 8xid:3 each) on this SQL quiz on plsqlchallenge.

Answer 2

As an alternative to Vincents's 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