如何对多列数据分组汇总？按产品分组并按型号统计数量

原表 tblStore #

原错误SQL语句 #

Select Product, 
    Sum(Case When tblStore.Quantity  = 'M1' Then 1 Else 0 End) As M1,
    Sum(Case When tblStore.Quantity  = 'M2' Then 1 Else 0 End) As M2,
    Sum(Case When tblStore.Quantity  = 'M3' Then 1 Else 0 End) As M3
From tblStore
Group by Name

预期输出 #

修正后的SQL语句 #

原SQL存在三处关键错误：

• 字段引用错误：表中不存在Quantity字段，需改为Model；
• 聚合逻辑错误：原语句统计的是符合条件的行数（用Then 1），实际需要累加Qty字段的值；
• 分组字段错误：Group by Name需改为Group by Product，表中无Name字段。

修正后的代码：

SELECT 
    Product,
    SUM(CASE WHEN Model = 'M1' THEN Qty ELSE NULL END) AS M1,
    SUM(CASE WHEN Model = 'M2' THEN Qty ELSE NULL END) AS M2,
    SUM(CASE WHEN Model = 'M3' THEN Qty ELSE NULL END) AS M3
FROM tblStore
GROUP BY Product

执行该语句后即可得到符合预期的结果。若需要将NULL显示为空字符串，可使用COALESCE函数进行转换，例如：

SELECT 
    Product,
    COALESCE(SUM(CASE WHEN Model = 'M1' THEN Qty END), '') AS M1,
    COALESCE(SUM(CASE WHEN Model = 'M2' THEN Qty END), '') AS M2,
    COALESCE(SUM(CASE WHEN Model = 'M3' THEN Qty END), '') AS M3
FROM tblStore
GROUP BY Product

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