MySQL对一列进行求和，并按两列分组 - MySQL sum up a column and group by two columns

MySQL sum up a column and group by two columns

本文关键字：两列一列 MySQL 求和 | 更新日期: 2023-09-27

我有一个表，结构如下:

Product       | Status        | Quantity
____________________________________________
P001          | 1             | 50
P001          | 1             | 30
P001          | 2             | 40
P001          | 2             | 60

我想把按产品和状态分组的数量加起来。结果将是:

Product       | Status 1 QTY   | Status 2 QTY
____________________________________________
P001          | 80             | 100

上述可以在一个sql查询?

谢谢!

可以通过CASE语句实现。

SELECT Product,
       SUM(CASE WHEN Status = 1 THEN Quantity ELSE 0 END) `Status 1 QTY`,
       SUM(CASE WHEN Status = 2 THEN Quantity ELSE 0 END) `Status 2 QTY`
FROM   TableName
GROUP  BY Product

如果Status有未知的值，最好动态执行