How to substract values in columns from two different tables in MySQL?

I am new to MySQL and I am trying to achieve the following.

From the following table of transactions between two users, write a query to return the change in net worth for each user, ordered by decreasing net change.

transactions1
+--------+----------+--------+------------------+
| sender | receiver | amount | transaction_date |
+--------+----------+--------+------------------+
|      5 |        2 |     10 | 2-12-20          |
|      1 |        3 |     15 | 2-13-20          |
|      2 |        1 |     20 | 2-13-20          |
|      2 |        3 |     25 | 2-14-20          |
|      3 |        1 |     20 | 2-15-20          |
|      3 |        2 |     15 | 2-15-20          |
|      1 |        4 |      5 | 2-16-20          |
+--------+----------+--------+------------------+

From here I have created two views, one with the users that have sent something and the total sent, and another with the users that have received something and the total received. User 5 has not received anything and user 4 has not sent anything so they are not in received/sent views respectively.

sent
+--------+------------+
| sender | total_sent |
+--------+------------+
|      1 |         20 |
|      2 |         45 |
|      3 |         35 |
|      5 |         10 |
+--------+------------+

received
+----------+----------------+
| receiver | total_received |
+----------+----------------+
|        1 |             40 |
|        2 |             25 |
|        3 |             40 |
|        4 |              5 |
+----------+----------------+

I’m trying to add both users so we have a complete list of users and to substract total_sent from total_received but im not getting a complete list of users. The users that are not in both tables are missing from my result and I’m not sure how can i approach this problem differently.

SELECT coalesce(sender,receiver) AS 'user', 
coalesce(total_received,0) - coalesce(total_sent,0) AS 'net_change'
FROM sent s
JOIN received r
ON s.sender = r.receiver
ORDER BY 2 DESC;


RESULT:
+------+------------+
| user | net_change |
+------+------------+
|    1 |         20 |
|    3 |          5 |
|    2 |        -20 |
+------+------------+

Any help is highly appreciated.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Instead of doing a join, you may perform a union on both sent and received aggregates first and a group by on the resulting union to get the results. The key here is that total_sent is summed as a negative value.

Option 1: Creating a union on sent and received before determining net_change

SELECT
    `user`,
    SUM(`change`) as net_change
FROM (
    SELECT sender as `user`, total_sent*-1 as `change` FROM sent UNION ALL
    SELECT receiver as `user`, total_received as `change` FROM received
) t
GROUP BY 
    `user`
ORDER BY 
    SUM(`change`) DESC;

Option 2: Determining net_change from the initial table

SELECT
    `user`,
    SUM(`change`) as net_change
FROM (
    SELECT sender as `user`, amount*-1 as `change` FROM transactions1 UNION ALL
    SELECT receiver as `user`, amount as `change` FROM transactions1
) t
GROUP BY 
    `user`
ORDER BY 
    SUM(`change`) DESC;

View on DB Fiddle

Let me know if this works for you.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0