Select the difference between money sent and money received in one table
So, I have this request:
SELECT curr, from, to, SUM(amount) AS total
FROM transfer
GROUP BY curr, from, to
ORDER BY curr, from
With this result:
| curr | from | to | total |
| USD | A | B | 1200.00 |
| USD | B | A | 500.00 |
I want it:
| curr | from | to | total |
| USD | B | A | 700.00 |
Sent 1200 to B and B sent 500 to A. So B still owes 700 to A.
In other words, I want the difference between the results when you have them grouped by curr, from and to. Note that IFs with only "A" or "B" values will not work, as FROM and TO have many other options.
Is this even possible in a MySQL query? I would like to achieve this in the Doctrine too.
+3
source to share
2 answers
Imagine starting at 0. If money goes from A to B, it should be added. If money is sent from B to A, it should be removed.
You can accomplish this using MySQL with a CASE statement inside your SUM () function, which will add / subtract money as needed. Try the following:
SELECT
SUM(
CASE WHEN from = 'A' AND to = 'B' THEN amount
WHEN from = 'B' AND to = 'A' THEN 0 - amount END)
AS total
FROM myTable;
Here is a SQL Fiddle example .
+1
source to share
Explanation
- Find and aggregate all transactions that have the same currency, source and destination
- On the left, join this result with yourself, crossing the source-destination, so we have general transactions between the destination-destination pair.
- Underline the bottom line and you have debt
- We only look at positive debts, so we recognize who owes who (we could do that with negative debts, but it's easier).
- Now you know who owes who and how much, just show the result (given that in both cases there are no transactions that AKA recognizes NULL results)
SELECT
T1.curr,
IFNULL(T2.from,T1.to) as `from`,
IFNULL(T2.to,T1.from) as `to`,
IFNULL((T1.total - T2.total),T1.total) as total
FROM
(
SELECT curr,`from`,`to`,SUM(total) as total
FROM transfer T1
GROUP BY curr,`from`,`to`
) T1
LEFT JOIN (
SELECT curr,`from`,`to`,SUM(total) as total
FROM transfer T1
GROUP BY curr,`from`,`to`
) T2
ON T1.curr = T2.curr AND T1.from = T2.to AND T1.to = T2.from
HAVING total > 0
ORDER BY curr
Here is the SQL Fiddle .
0
source to share