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.

CodePudding user response：

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.