MYSQL:具有多个组的 SUM

Question

我有这样的表:

PRODUCTID      REORDER_QTY      TYPE      ADJUST_TYPE  
-------------  ---------------  --------  -----------------
         4343             2.00  Transfer  ADD              
         4730             2.00  Transfer  ADD              
         4730             2.00  Transfer  ADD              
         4730             1.00  Transfer  DEDUCT           
         3643            20.00  Transfer  ADD              
         3643             3.00  Transfer  DEDUCT           

我如何编写一个 sql 来根据 PRODUCTID 和 ADJUST_TYPE 来计算 REORDER_QTY 的总和，然后通过总和得到它的余额ADD 和减去 DEDUCT

的总和

我想要的示例 sql 结果输出:

PRODUCTID        TYPE                   Total_Balance
-------------    ----------             ---------------
4730             Transfer               ((SUM the REORDER_QTY by ADD) - (SUM the REORDER_QTY by DEDUCT))
3643             Transfer               ((SUM the REORDER_QTY by ADD) - (SUM the  REORDER_QTY by DEDUCT))
4343             Transfer               ((SUM the REORDER_QTY by ADD) - (SUM the  REORDER_QTY by DEDUCT))

任何帮助都会很棒，谢谢

Answer 1

您只需要条件聚合:

select productid, type,
       sum(case when adjust_type = 'ADD' then reorder_qty
                when adjust_type = 'DEDUCT' then - reorder_qty
                else 0
           end) as total_balance
from atable
group by productid, type;