CAST Function using BIGINT

Question

Unable to run the below code.

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)

ERROR:

Arithmetic overflow error converting expression to data type int.

Answer 1

You need to cast before you sum to avoid that error.

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