Query to get column totals and Subtract MySQL

Question

I have a transaction table

I am able to get all the debit and credit transaction totals by property separately using these queries

SELECT property, SUM(amount) as creditamount FROM transactions WHERE transactions.type="CREDIT" GROUP BY property    

SELECT property, SUM(amount) as debitamount FROM transactions WHERE transactions.type="DEBIT" GROUP BY property

I am facing difficulty in having these two queries done in a single query so that i can subtract credit amount and debit amount for each row and list it according to the property

I tried using sub queries but multiple rows are returned.

Any way to achieve this?

score 0 · Answer 1 · answered Mar 28 '18 at 05:32

Use self join

select debitamount , creditamount
from (select sum(amount) income
     from  transactions 
     WHERE transactions.type="CREDIT"  
     GROUP BY property) i
JOIN (select sum(rate) debitamount 
     from transactions 
     WHERE transactions.type="DEBIT" 
     GROUP BY property) e

Refer this question

score 0 · Accepted Answer · answered Mar 28 '18 at 07:45

Conditional aggregation may be what you are looking for.

SELECT property, 
 SUM(case when type = 'Credit' then amount else 0 end) as creditamount,
 SUM(case when type = 'Debit' then amount else 0 end) as debitamount,
 SUM(case when type = 'Credit' then amount else 0 end) -
 SUM(case when type = 'Debit' then amount else 0 end) as  diff
 FROM transactions 
 GROUP BY property

Query to get column totals and Subtract MySQL

2 Answers2