如何在SQL中计算到货总金额减发货总金额并修正分组错误？

问题背景 #

需要计算coins表中指定条件下到货（spended=0）的amount总和减去发货（spended=1）的amount总和，原始查询语句如下：

SELECT amount, conamt, id, spended, cspended
FROM coins 
WHERE id = 'D00' AND idntf IN ('DFA0D02A', ...);

此前采用的分组方式会在同一天同一批次（conamt）金额相同时出现计算错误：

GROUP BY amount, conamt
HAVING COUNT(*) = 1;

错误原因 #

把amount作为分组字段，再配合HAVING COUNT(*) = 1的过滤条件，会导致同一批次下金额相同的多条有效记录被排除。比如同一批次有2笔金额相同的到货记录，COUNT(*)等于2，不满足HAVING条件，这部分金额就不会被计入总和，最终结果自然出错。

正确解决方案 #

方案1：直接计算全局总差值 #

使用条件聚合直接计算全局的到货减发货金额差值，无需错误的分组逻辑：

SELECT 
    SUM(CASE WHEN spended = 0 THEN amount ELSE 0 END) - 
    SUM(CASE WHEN spended = 1 THEN amount ELSE 0 END) AS total_balance
FROM coins 
WHERE id = 'D00' AND idntf IN ('DFA0D02A', ...);

方案2：按批次（conamt）分组计算差值 #

如果需要按批次维度统计每个批次的金额差值，只需按conamt分组，保留条件聚合逻辑：

SELECT 
    conamt,
    SUM(CASE WHEN spended = 0 THEN amount ELSE 0 END) - 
    SUM(CASE WHEN spended = 1 THEN amount ELSE 0 END) AS batch_balance
FROM coins 
WHERE id = 'D00' AND idntf IN ('DFA0D02A', ...)
GROUP BY conamt;

这两种方式都会保留所有有效记录，不会因为金额相同而过滤数据，确保计算结果准确。

内容的提问来源于stack exchange，提问作者Yuliya M