mysql - 查询获取列总计并减去 MySQL

Question

我有一个交易表

我可以使用这些查询分别获取按属性(property)划分的所有借方和贷方交易总额

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

我在单个查询中完成这两个查询时遇到困难，以便我可以减去每行的贷方金额和借方金额并根据属性列出它

我尝试使用子查询，但返回了多行。

有什么办法可以实现这个目标吗？

Answer 1

条件聚合可能正是您正在寻找的。

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