SQL出现算术溢出错误

Question

当运行一个简单的聚合函数时（见下面的代码），我遇到了“算术溢出错误，将表达式转换为数据类型int”的错误。随着我缩小日期范围，我可以看到结果实际上超过了INT数据类型的限制...我尝试将整个表达式转换为BIGINT但没有成功。有人知道我如何解决这个问题吗？

这3个查询都会抛出相同的上述错误：

Select  (SUM(ACDTalkTimeInSec) + SUM(TotAcwTimeInSec) + SUM(HoldTimeInSec)) as AHT
From ColTelephony.dbo.vwACDSkillCombined acd
Where WorkDte between '1/2/2018' AND '10/5/2018'

Select  CAST(Cast(SUM(ACDTalkTimeInSec) AS BIGINT) + CAST(SUM(TotAcwTimeInSec) AS BIGINT) + CAST(SUM(HoldTimeInSec) AS BIGINT) AS BIGINT) as AHT
From ColTelephony.dbo.vwACDSkillCombined acd
Where WorkDte between '1/2/2018' AND '10/5/2018'

Select  Cast(((SUM(ACDTalkTimeInSec) + SUM(TotAcwTimeInSec) + SUM(HoldTimeInSec))) AS BIGINT) as AHT
From ColTelephony.dbo.vwACDSkillCombined acd
Where WorkDte between '1/2/2018' AND '10/5/2018'

- wlfente

---

计算总和前需要先进行类型转换，而不是之后。 - SMor

谢谢！完美地工作了！我讨厌答案像这样简单哈哈。请发布为答案，我会标记它的。再次感谢！ - wlfente

Answer 1

SMor在评论中已经回答了，但以下是SQL语句：

Select SUM(CAST(ACDTalkTimeInSec AS BIGINT)) + SUM(CAST(TotAcwTimeInSec AS BIGINT)) 
    + SUM(CAST(HoldTimeInSec AS BIGINT)) as AHT
From ColTelephony.dbo.vwACDSkillCombined acd
Where WorkDte between '1/2/2018' AND '10/5/2018'

如果SMor发布了他们的答案，请接受它。我没有测试代码，所以请原谅任何拼写错误。

- Michael Bruesch

---

我正在寻找相同的问题。谢谢@Michael - Jeeva N