MySQL JOINING same table returns wrong result on SUM()

Question

I have table Clients and Orders

"Clients"             |     "Orders"

ID     NAME           |     ID    SENDER_CLIENT_ID      RECEIVER_CLIENT_ID     PRICE
1      Alex           |     1           1                       3               100
2      Scott          |     2           2                       3               300
3      Philipp        |     3           1                       2               200
                      |     4           1                       2               400
                      |     5           3                       1               300

Expected Output:

ID    NAME         SENT_AMOUNT      RECEIVED_AMOUNT
1     Alex            700               300
2     Scott           300               600
3     Philipp         300               400

MY Query Output:

ID    NAME         SENT_AMOUNT      RECEIVED_AMOUNT
1     Alex            700               900
2     Scott           600               600
3     Philipp         600               400

MySQL Query

SELECT c.id, name, sum(o.price) sent_amount, sum(o2.price) received_amount
FROM clients c LEFT JOIN orders o on c.id = o.sender_client_id LEFT JOIN orders o2 on c.id = t2.receiver_client_id
GROUP BY c.id

When I join "ORDERS" table the second time, there is the issue i think as it may bring duplicate values and from there it SUMs() the duplicates. How to fix?

Answer 1

try to sum amounts independently

select a.*, s.price send, t.price receive
from clients a 
left join (
  select sender_client_id, sum(price) price
  from orders
  group by sender_client_id
) s on a.id = s.sender_client_id
left join (
  select receiver_client_id, sum(price) price
  from orders
  group by receiver_client_id
) t on a.id = t.receiver_client_id