I have a table that looks something like this:
| sender | reciever | amount |
|---|---|---|
| 1 | 2 | 10 |
| 2 | 1 | 20 |
| 3 | 2 | 20 |
| 1 | 3 | 30 |
The desired output should be:
| user | Trans_Change |
|---|---|
| 1 | -20 |
| 2 | 10 |
| 3 | 10 |
i can't find a way to write a query for it in SQL. the logic behind the desired output should be that; 1 sends 2 amount of 10, so now 1 has: -10 and 2 has: 10 and so on...
CodePudding user response：
Best Guess given known info:
We simply assign all senders negative transaction amounts union all the receivers as positive amounts and then group the data summing the transactions
With CTE AS (
SELECT sender as aUser, (-1 * amount) as Trans_Change -- Senders lose money
FROM table
UNION ALL
SELECT Receiver as aUser, amount -- receivers get money
FROM Table)
SELECT aUser, sum(Trans_Change) as Trans_Change -- aggregate transaction totals by user
FROM CTE
GROUP BY aUser
Part of addressing this is acknowledging that an amount is being used twice: once for the sender as a negative, once for a receiver as a positive (or credit/debit if you prefer) Realizing this I knew I needed to get that value on two separate rows. selecting the data twice allowed for this. Using two selects and a union all allows us to get that value twice and then it's a simple aggregration.