表中单列中的 MYSQL 数学.子查询未正确分组

Question

我正在尝试对名为"actions"的单个表中的单个列执行数学运算。 该表如下所示：

Shopper ID   ItemID  Action
1            200     Purchase
1            200     Purchase
1            200     Return
2            100     Purchase
2            100     Return
3            200     Purchase
3            100     Purchase

我想从上面的表格结构中获取每个itemID的净购买数量，即购买减去退货。 每行是一个事务，所以我使用计数来派生数量。 所需的结果如下所示：

ItemID   Qty_Purch   Qty_returned  Net_qty_purchased
100      2           1             1
200      3           1             2

我下面的查询有一个分组问题，它为每个itemID添加了整个表中的所有购买和所有退货，所以我得到的结果是这样的：

ItemID   Qty_Purch   Qty_returned  Net_qty_purchased
100      5           2             3
200      5           2             3

我当前的查询如下所示：

create table MYDB.purch0 as
SELECT ItemId,
(Select Count(w1.ItemID) FROM MYDB.actions w1 where w1.action in ("Purchase")) as Qty_Purch,
(Select Count(w2.ItemID) FROM MYDB.actions w2 where w2.action in ("Return  ") ) as Qty_Returned,
(Select  Count(w3.ItemID) FROM MYDB.actions w3 where w3.action in ("Purchase")) -
(Select  Count(w4.ItemID) FROM MYDB.actions w4 where w4.action in ("Return  "))  as Net_qty_purchased
FROM MYDB.actions w
group by itemID

请帮助我获得所需的输出。 谢谢期待。

Answer 1

您可以使用条件聚合来获取所需的结果，利用MySQL在数字上下文中将布尔值视为1或0的事实：

SELECT ItemID,
SUM(Action = 'Purchase') AS Qty_purch,
SUM(Action = 'Return') AS Qty_returned,
SUM(Action = 'Purchase') - SUM(Action = 'Return') AS Net_qty_purchased
FROM actions
GROUP BY ItemID

输出：

ItemID  Qty_purch   Qty_returned    Net_qty_purchased
100     2           1               1
200     3           1               2

在 dbfiddle 上演示