按仓库汇总收发交易的库存余额报表：如何计算库存总计？

你的问题出在分组条件太冗余，当前GROUP BY里包含了TO_LOCN_ID、TO_LOCN_NAME、FROM_LOCN_ID、FROM_LOCN_NAME，这会导致同一个仓库因为收发方向不同被拆分成多条记录，无法直接得到该仓库的总计库存。

可以通过以下两种方式修改SQL：

方式一：先统一仓库标识，再按仓库汇总 #

先通过子查询把每条交易对应的仓库（入库取TO，出库取FROM）和对应的数量（入库为正，出库为负）整理好，再按仓库分组求和：

SELECT 
    ITEM_CODE,
    ANBAR,
    `Anbar Adı`,
    SUM(SUMTOT) AS 库存总计
FROM (
    SELECT 
        MOL.ITEM_CODE,
        CASE WHEN MOL.TO_LOCN_ID IS NOT NULL THEN MOL.TO_LOCN_ID ELSE MOL.FROM_LOCN_ID END AS ANBAR,
        CASE WHEN MOL.TO_LOCN_NAME IS NOT NULL THEN MOL.TO_LOCN_NAME ELSE MOL.FROM_LOCN_NAME END AS `Anbar Adı`,
        CASE WHEN MOL.TO_LOCN_ID IS NOT NULL THEN DEMAND_QTY ELSE -DEMAND_QTY END AS SUMTOT
    FROM AZP_MOL MOL 
    WHERE ITEM_CODE = '1010000003'
) t
GROUP BY ITEM_CODE, ANBAR, `Anbar Adı`
ORDER BY ANBAR ASC;

方式二：直接在主查询中按统一后的仓库字段分组 #

调整GROUP BY的字段，只保留统一后的仓库标识和名称，同时确保聚合函数正确计算：

SELECT 
    MOL.ITEM_CODE,
    CASE WHEN MOL.TO_LOCN_ID IS NOT NULL THEN MOL.TO_LOCN_ID ELSE MOL.FROM_LOCN_ID END AS ANBAR,
    CASE WHEN MOL.TO_LOCN_NAME IS NOT NULL THEN MOL.TO_LOCN_NAME ELSE MOL.FROM_LOCN_NAME END AS `Anbar Adı`,
    SUM(CASE WHEN MOL.TO_LOCN_ID IS NOT NULL THEN DEMAND_QTY ELSE -DEMAND_QTY END) AS 库存总计
FROM AZP_MOL MOL 
WHERE ITEM_CODE = '1010000003'
GROUP BY MOL.ITEM_CODE, 
         CASE WHEN MOL.TO_LOCN_ID IS NOT NULL THEN MOL.TO_LOCN_ID ELSE MOL.FROM_LOCN_ID END,
         CASE WHEN MOL.TO_LOCN_NAME IS NOT NULL THEN MOL.TO_LOCN_NAME ELSE MOL.FROM_LOCN_NAME END
ORDER BY ANBAR ASC;

两种方式都能把同一个仓库的所有收发交易汇总，得到最终的库存余额（比如你的'AA001TX'仓库会得到总计2的结果）。区别在于第一种可读性更强，第二种少一层子查询，性能差异不大，可根据实际情况选择。

内容的提问来源于stack exchange，提问作者Vugar Bakhishov