选择在同一表格中记录的已发送货币和收到的货币的差额

So, I have this query:

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  |

What I want is this:

| curr | from | to | total  |    
| USD  | B    | A  | 700.00 |

A 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 the grouping of them by curr, from and to. Have in mind that an IF with just the values 'A' or 'B' would not work, as FROM and TO has a lot of other options.

Is this even possible on a MySQL query? I would like to achieve this in Doctrine as well.

Imagine starting at 0. If money is sent 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 of your SUM() function, that will add/subtract money as necessary. Try this:

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 an SQL Fiddle example.

Explanation

1. Find and aggregate all the transactions that have the same currency, origin and destination
2. Left join that result with itself, crossing origin-destination, that way we have the total transactions between origin-destination pair.
3. Substract the totals and you have the debt
4. We consider only positive debts, that way we recognize who owes who (we could have done it considering negative debts, but is easier this way)
5. Now yo know who owes who and how much, just show the result (considering when there are no transactions both ways AKA recognizing 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.