How to get sum of table a and sum of table b with multiple rows

Question

I have two tables:

table1 as a

id	date	customer	amount
1	2021-04-08	a	2
2	2021-04-08	a	3

table2 as b

id	orderid	money
1	1	50
2	1	60
3	2	10
4	2	20

Now I want to have the date, customer and the sum of amount from table1 and the sum of money from table2. That means, the result should look like this:

date	customer	amount	money
2021-04-08	a	5	140

But with the following query I get an amount of 10:

 select
      a.date,
      a.customer,
      sum(b.money) as money,       
      sum(a.amount) as amount
  from table1 a
    left join table2 b on a.id = b.orderid   
  group by date, customer

I don't know, how to solve this.

Answer 1

This is tricky. You need to aggregate before joining:

select a.date, a.num_customer, a.a_amount, b.b_amount
from (select a.date, count(*) as num_customer, sum(a.amount) as a_amount
      from table1 a
      group by a.date
     ) a left join
     (select a.date, sum(b.amount) as b_amount
      from table1 a join
           table2 b
           on a.id = b.orderid
      group by a.date
     ) b
     on a.date = b.date;

An alternative method is to aggregate the second table by orderid first and then join and aggregate again:

select a.date, count(*) as num_customer, sum(a.amount),
       sum(b.b_amount)
from table1 a left join
     (select b.orderid, sum(b.amount) as b_amount
      from table2 b
      group by b.orderid
     ) b
     on a.id = b.orderid
group by a.date