Sql query to find total balance

Question

I have below table:

If given [portfolio_id] P1, return the total balance value of all funds linked to P1, or linked to any sub_portfolio_id that is eventually linked up to P1? This includes all funds linked to P1, P2, P3, and P4.

Answer 1

You can generate the portfolios equivalent to "1" using a recursive CTE. Then just use this for the aggregation:

with portfolios as (
      select 'p1' as portfolio_id
      union all
      select t.sub_portfolio_id
      from portfolios p join
           t
           on p.portfolio_id = t.portfolio_id
      where t.sub_portfolio_id is not null
     )
select sum(t.balance)
from t
where t.portfolio_id in (select cte.portfolio_id from cte);

Here is a SQL Fiddle that shows the portfolios CTE.

Answer 2

Try this one.

with
    PortfolioCTE (portfolio_id, sub_portfolio_id, balance)
    as
    (
        select portfolio_id, sub_portfolio_id, balance from Table_1
        where portfolio_id = 'p1'

        union all 

        select Table_1.portfolio_id, Table_1.sub_portfolio_id , Table_1.balance 
        from Table_1
        join PortfolioCTE 
        on Table_1.portfolio_id = PortfolioCTE.sub_portfolio_id
    )

SELECT portfolio_id, balance FROM PortfolioCTE
WHERE balance IS NOT NULL
ORDER BY portfolio_id

---- Total Balance (uncomment this to get total balance)
--SELECT SUM(CONVERT(INT, LEFT(balance,CHARINDEX(' ', balance)))) 'TotalBalance'
--FROM PortfolioCTE
--WHERE balance IS NOT NULL

Results

Explanation: https://youtu.be/GGoV0wTMCg0