SQL Server 2014：基于另一列求和报错（varchar转real失败）

执行的SQL语句 #

SELECT 
    HCT_USER_CONTRACT_NUMBER AS ContractNumber, 
    SUM(CASE 
            WHEN HIT_CURRENT_STOCK_NUMBER = '_DELIVERY' 
                THEN HIT_CHARGE_AMOUNT 
                ELSE 0 
        END) AS [DeliveryCharge]
FROM 
    TH_HIRE_CONTRACTS C 
LEFT JOIN 
    TH_HIRE_ITEMS I on C.HCT_CONTRACT_NUMBER = I.HIT_CONTRACT_NUMBER
GROUP BY 
    HCT_USER_CONTRACT_NUMBER

报错信息 #

Error converting data type varchar to real

需求说明 #

TH_HIRE_CONTRACTS表的每条记录对应TH_HIRE_ITEMS表的多条数据，其中租赁物品、配送、回收各占一行。需要按合同号聚合，仅汇总其中HIT_CURRENT_STOCK_NUMBER为'_DELIVERY'的HIT_CHARGE_AMOUNT值，每个合同返回一条记录。

解决方法 #

报错原因是HIT_CHARGE_AMOUNT字段为varchar类型，直接参与SUM运算时SQL会自动尝试将其转为数值类型，若字段中存在无法转换的内容就会触发报错。需要显式转换该字段为数值类型，推荐用TRY_CAST避免转换失败导致整个查询中断：

SELECT 
    HCT_USER_CONTRACT_NUMBER AS ContractNumber, 
    SUM(CASE 
            WHEN HIT_CURRENT_STOCK_NUMBER = '_DELIVERY' 
                THEN TRY_CAST(HIT_CHARGE_AMOUNT AS DECIMAL(18,2)) 
                ELSE 0 
        END) AS [DeliveryCharge]
FROM 
    TH_HIRE_CONTRACTS C 
LEFT JOIN 
    TH_HIRE_ITEMS I on C.HCT_CONTRACT_NUMBER = I.HIT_CONTRACT_NUMBER
GROUP BY 
    HCT_USER_CONTRACT_NUMBER

如果确定HIT_CHARGE_AMOUNT的所有有效值都能正常转换，也可以使用CAST或CONVERT：

SUM(CASE 
        WHEN HIT_CURRENT_STOCK_NUMBER = '_DELIVERY' 
            THEN CAST(HIT_CHARGE_AMOUNT AS DECIMAL(18,2)) 
            ELSE 0 
    END) AS [DeliveryCharge]

另外，也可以先过滤出配送行再聚合，减少不必要的计算：

SELECT 
    C.HCT_USER_CONTRACT_NUMBER AS ContractNumber, 
    SUM(TRY_CAST(I.HIT_CHARGE_AMOUNT AS DECIMAL(18,2))) AS [DeliveryCharge]
FROM 
    TH_HIRE_CONTRACTS C 
LEFT JOIN 
    TH_HIRE_ITEMS I on C.HCT_CONTRACT_NUMBER = I.HIT_CONTRACT_NUMBER
        AND I.HIT_CURRENT_STOCK_NUMBER = '_DELIVERY'
GROUP BY 
    C.HCT_USER_CONTRACT_NUMBER

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