VARCHAR列SUM CASE WHEN报错：如何统计自动/手动状态行数？

报错原因 #

SUM函数仅支持数值类型运算，你之前的代码在CASE语句中返回的是'AUTOMATIC'或'MANUAL'字符串类型，这才是触发Operand data type varchar is invalid for sum operator错误的原因，和Notes列本身的varchar类型无关。

解决方案 #

方案一：直接统计各状态行数（无需预生成Status列） #

直接在SUM的CASE表达式中返回数值1或0，分别对应符合/不符合条件的行，求和后得到对应状态的行数：

SELECT 
    a.CustomerID,
    SUM(CASE WHEN A.NOTES LIKE '%AUTOMATIC%' THEN 1 ELSE 0 END) AS Automatic_Count,
    SUM(CASE WHEN A.NOTES NOT LIKE '%AUTOMATIC%' THEN 1 ELSE 0 END) AS Manual_Count
FROM CARS.DBO.REVIEW AS A
GROUP BY A.CustomerID

方案二：基于预生成的Status列统计 #

如果需要先生成Status列再统计，可通过子查询或CTE先构建包含Status列的临时数据集，再分组统计：

子查询方式

SELECT 
    CustomerID,
    SUM(CASE WHEN Status = 'AUTOMATIC' THEN 1 ELSE 0 END) AS Automatic_Count,
    SUM(CASE WHEN Status = 'MANUAL' THEN 1 ELSE 0 END) AS Manual_Count
FROM (
    SELECT 
        CustomerID,
        CASE WHEN NOTES LIKE '%AUTOMATIC%' THEN 'AUTOMATIC' ELSE 'MANUAL' END AS Status
    FROM CARS.DBO.REVIEW
) AS sub_query
GROUP BY CustomerID

CTE方式

WITH ReviewWithStatus AS (
    SELECT 
        CustomerID,
        CASE WHEN NOTES LIKE '%AUTOMATIC%' THEN 'AUTOMATIC' ELSE 'MANUAL' END AS Status
    FROM CARS.DBO.REVIEW
)
SELECT 
    CustomerID,
    SUM(CASE WHEN Status = 'AUTOMATIC' THEN 1 ELSE 0 END) AS Automatic_Count,
    SUM(CASE WHEN Status = 'MANUAL' THEN 1 ELSE 0 END) AS Manual_Count
FROM ReviewWithStatus
GROUP BY CustomerID

核心逻辑说明 #

两种方案的核心都是利用SUM(CASE ... THEN 1 ELSE 0 END)统计行数：当行符合目标状态条件时返回1，否则返回0，求和结果即为该状态的总行数。

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