CODEWITHSUNDEEP
mysqlsql
David P.
David P.

Reputation: 117

SQL crosstab query to see the sum of some transactions

I have an SQL database table containing money transfer transactions. I have 4 columns: TransferID, Payer, Payee, Amount

Let's say this is my database table:

DatabaseTable
(source: pbrd.co)

I can create a crosstab query to see how much money was sent by each guy to each of his buddy. The result will be like something like this:

ResultOfCrosstabQuery
(source: pbrd.co)

However what I want to see is the balance of transactions between each two guys. For example if Peter sent $13 to John and John sent $2 to Peter then I want to see $11 (and $-11) as the summarized result of their transactions instead of $13 and $2. The result should look something like this:
ResultTable
(source: pbrd.co)

What query could make the trick?

Upvotes: 1

Views: 638

Answers (2)

Parfait
Parfait

Reputation: 107697

As mentioned, the dynamic, pivot crosstab query using the TRANSFORM clause is uniquely an MS Access SQL method unavailable in other RDMS's. Since OP has linked MySQL backend tables to an MS Access frontend app, a frontend crosstab query can be run on MySQL data.

For specific needs, consider a source query that joins aggregate queries of matching Payer and Payee. Then run a crosstab on source query:

Source Query

SELECT m1.Payer, m1.Payee, (m1.SumAmount - m2.SumAmount) As NetTransfer
FROM 
  (SELECT t.Payer, t.Payee, Sum(t.Amount) AS SumAmount
   FROM Transfers t
   GROUP BY t.Payer, t.Payee) m1
INNER JOIN
  (SELECT t.Payer, t.Payee, Sum(t.Amount) AS SumAmount
   FROM Transfers t
   GROUP BY t.Payer, t.Payee) m2
ON m1.Payer = m2.Payee AND m1.Payee = m2.Payer

-- Payer    Payee   NetTransfer
-- John     Fred              2
-- Fred     John             -2
-- Peter    John             11
-- John     Peter           -11

Crosstab Query (syntax is only valid in MS Access):

TRANSFORM Sum(q.NetTransfer) AS SumOfNetTransfer
SELECT q.Payer
FROM SourceQueryQ q
GROUP BY q.Payer
PIVOT q.Payee;

-- Payer    Fred    John    Peter
-- Fred               -2    
-- John        2              -11
-- Peter              11

Of course, first query can also be nested as a derived table in crosstab:

Combined Query

TRANSFORM Sum(q.NetTransfer) AS SumOfNetTransfer
SELECT q.Payer
FROM 
   (SELECT m1.Payer, m1.Payee, (m1.SumAmount - m2.SumAmount) As NetTransfer
    FROM 
       (SELECT t.Payer, t.Payee, Sum(t.Amount) AS SumAmount
       FROM Transfers t
       GROUP BY t.Payer, t.Payee) m1
    INNER JOIN
      (SELECT t.Payer, t.Payee, Sum(t.Amount) AS SumAmount
       FROM Transfers t
       GROUP BY t.Payer, t.Payee) m2
    ON m1.Payer = m2.Payee AND m1.Payee = m2.Payer) q
GROUP BY q.Payer
PIVOT q.Payee;

NOTE: As in any Access query, the crosstab is limited to 255 columns, so if data contains more than 254 distinct Payers/Payees, use the PIVOT...IN clause to define columns:

PIVOT q.Payee IN ('Fred', 'John', 'Peter')

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522292

A pivot query should do the trick:

SELECT Payer,
       SUM(CASE WHEN Payee = 'Peter' THEN Amount END) AS Peter,
       SUM(CASE WHEN Payee = 'John'  THEN Amount END) AS John,
       SUM(CASE WHEN Payee = 'Fred'  THEN Amount END) AS Fred
FROM yourTable
GROUP BY Payer

Depending on the database you are using, you might be able to take advantage of some built in pivot capability. Also, formatting the output as a currency, or with dashes for no amount, would be database specific.

Upvotes: 0

Related Questions