使用BIGINT的CAST函数

Question

无法运行下面的代码。

Select Month(ROW_DATE) as Month, cast(sum(da.ASSISTS) as bigint)/cast(nullif(sum(da.acdcalls),0) as bigint) as [Assist%]
FROM 
[D998AA0_TELECOM].[APD_TEL].[TCMS_DAGENT] da 
JOIN [D998AA0_TELECOM].[CentralDWH].[Sessions_booked] sb ON sb.PBX_id = da.LOGID
JOIN [D998AA0_TELECOM].[APD_TEL].[TCMS_SKILL_DIM_KEY_NEW] dk ON cast(dk.HuntGrpExt as char) = sb.p7_value 
where IEXSkillName IN ('Universal', 'Tracking','Shipping')
and Channel = 'Phone'
and Country = 'US'
AND [ROW_DATE] NOT LIKE '%C%' AND CONVERT(date,ROW_DATE) BETWEEN '2022-01-01'  AND '2022-01-31'
GROUP BY Month(ROW_DATE)
ORDER BY Month(ROW_DATE)

错误:

将表达式转换为数据类型int时发生算术溢出错误。

Answer 1

您需要在sum之前执行cast以避免此错误。

sum(cast(assists as bigint)) / nullif(sum(cast(acdcalls as bigint)),0) as [Assist%]