Find sum() from two different tables and join them based on a condition?

I have two tables,

Table1:

ID   Amount  Date
------------------
123  500.00  02-Sep-2020
123  240.00  02-Sep-2020
124  200.50  02-Sep-2020
125  150.70  03-Sep-2020
123  480.80  03-Sep-2020

Table2

ID   Settled_Amount  Date
-------------------------------
123    150.25       02-Sep-2020
124    200.00       03-Sep-2020
125    100.40       03-Sep-2020

I want to sum the Amount column of table1 and sum the settled_amount column of Table2 of a particular ID group by the Date column.

So My result would be for ID=123:

Sum(Amount) Sum(Settled_amount) Date
------------------------------------------
740.00       150.25             02-Sep-2020
480.80                          03-Sep-2020

   

Answer

You can use union all and group by. For all ids:

select id, date, sum(amount), sum(amount_settled)
from ((select id, date, amount, null as amount_settled
       from table1
      ) union all
      (select id, date, null as amount, amount_settled
       from table2
      ) 
     ) t
group by id, date
order by date;

You can filter for a particular id using a where clause in the outer query.