SUM WITH Left Join group by clause

Question

I have three tables which have a common column Ch_id. I want to sum the column(total) which is there in all three tables with alias (Allocation,Received,Withdrawl,Balance).

How can I join all three tables and get result like below.

CH_id      Allocation   Received   Withdrawl   Balance(Received-Withdrawl)
--------------------------------------------------------------------------
1          100000       50000       25000       25000
2          300000       20000       200000      0
3          200000       100000      0           100000

...... and so on

I want all the rows in CH_Allocation table

I have tried the following but it gives not so expected result.

SELECT
    CH_Allocation.CH_id,
    SUM(CH_Allocation.Total) AS Allocated,
    SUM(ch_funds.Total) AS received,
    (req_ch.total) AS Withdrawl
FROM CH_Allocation 
LEFT JOIN Req_CH
    ON Req_CH.CH_id = CH_Allocation.CH_id
LEFT JOIN CH_Funds
    ON CH_Funds.CH_id = CH_Allocation.CH_id
WHERE
    CH_Allocation.Project_Id = 2 AND
    Req_CH.Project_id = 2
GROUP BY
    CH_Allocation.CH_id,
    Req_CH.ch_id,
    CH_Funds.CH_id

and also this

select a.ch_id,SUM(a.total) as alloted,SUM(b.total) as received,SUM(c.total) as withdrawl,SUM(b.Total)-sum(c.Total) as balance from 
CH_Allocation a,CH_Funds b,Req_CH c
where a.CH_id=b.CH_id
and c.CH_id=b.CH_id
and b.CH_id=a.CH_id
and c.Project_id=2
and a.Project_Id=2
and b.Project_Id=2
group by a.CH_id

Please help experts.

Answer 1

There are two approaches to this. One is to use a full join, but that is rather messy because lots of NULL values are created. The second is to union the tables together and then aggregate:

SELECT CH_id,
       SUM(Allocated) AS Allocated,
       SUM(received) AS received,
       SUM(Withdrawal) AS Withdrawal
FROM ((SELECT Project_Id, ch_id, Total as allocated, 0 as received, 0 as Withdrawal
       FROM CH_Allocation 
      ) UNION ALL
      (SELECT Project_Id, ch_id, 0 as allocated, Total as received, 0 as Withdrawal
       FROM Req_CH 
      ) UNION ALL
      (SELECT Project_Id, ch_id, 0 as allocated, 0 as received, Total as Withdrawal
       FROM CH_Funds 
      ) 
     ) t
WHERE Project_Id = 2 
GROUP BY ch_id;