如何将正记录和负记录与计算所有余额总和的查询分开

Question

select account
     , SUM(balance) as bal
from Main_report
Group BY account

如何从上述查询中分离正面和负面记录？我想将此查询与下面列出的案例相结合。

select (case when bal < 0 then bal else 0 end) as debits,
(case when bal > 0 then bal else 0 end) as credits from Main_report
Group BY account

Answer 1

只需使用您的两个案例并汇总这些情况的结果：

SELECT account
     , SUM(CASE WHEN balance < 0 THEN balance ELSE 0 END) as debits
     , SUM(CASE WHEN balance > 0 THEN balance ELSE 0 END) as credits
FROM Main_report
GROUP BY account