Calculating Sum of Hierarchical Query, Grouped by Type

Question

I have a table TRANSFERS which contains these columns: account_id, father_account_id, amount, type.

This table represents all transfers of money made from the specific account, their amount, and what were they paying for by types (such as food/school/taxes etc).

What I need to do, is for every main account (an account without a parent) find out how much money was transfered from it (which means summing up and adding also transfers from its child accounts), grouped by the type.

For example:

account_id | type | amount

   1234    | food  | 500

   1234    | taxes | 750

   1111    | food  | 200

   1111    | school | 600

I've looked around and couldn't find a solution anywhere. Any help would be very much appriciated! Thanks in advance!

Answer 1

You could use operator connect_by_root and sum values for each root id and type:

select root account_id, type, sum(amount) amount
  from (
    select connect_by_root(account_id) root, type, amount
      from transfers
      connect by father_account_id = prior account_id and type = prior type
      start with father_account_id is null )
  group by root, type

^SQLFiddle