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.

Source: Symfony Questions

Was this helpful?

0 / 0

Leave a Reply 0

Your email address will not be published. Required fields are marked *