Mysql Minus Sum Of Two Different Columns With Join – Using Symfony Query Builder

I’ve got two tables:

Account: id, name
Transaction: account_id,payment,charge

The transactions links to the account, and the transaction can either be a payment or a charge. What I’m looking to do is in a single query, sum all the payments and minus the sum of all the charges so give the overall balance.

I’m not great with mysql queries when they get this complex, but I can get the sum of the payments easily enough:

SELECT SUM(I.payment), A.* FROM propertyLettingAccountNew A INNER JOIN lettings_account_statement_item I ON I.letting_account_id = A.id GROUP BY A.id

So this is giving me the over all payment amount but I’m not sure how to sum the charge column and subtract it from the payment sum. I’m going to be using the symfony query builder for running this query once it’s figured out if that makes a difference.

Answer

You can subtract charge from payment for each transaction and sum the results:

SELECT a.id, a.name,
       SUM(COALESCE(t.payment, 0) - COALESCE(t.charge, 0)) balance
FROM Account a INNER JOIN Transaction t
ON t.account_id = a.id
GROUP BY a.id, a.name 

I use COALESCE() just in case there are nulls in the columns.
If there aren’t any nulls:

SELECT a.id, a.name,
       SUM(t.payment - t.charge) balance
FROM Account a INNER JOIN Transaction t
ON t.account_id = a.id
GROUP BY a.id, a.name