SQL Server 返回“Arithmetic overflow error converting expression to data type int”。

一尘不染

sql-server

当我运行这个命令时SUM()

SELECT COUNT(*) AS [Records], SUM(t.Amount) AS [Total]
FROM   dbo.t1 AS t
WHERE  t.Id > 0
       AND t.Id < 101;

我越来越，

Arithmetic overflow error converting expression to data type int.

知道它的原因是什么吗？

阅读 160

2022-11-11

共1个答案

一尘不染

对于大于INT最大值 (2,147,483,647) 的值，您需要使用COUNT_BIG (*)。

SELECT COUNT_BIG(*) AS [Records], SUM(t.Amount) AS [Total]
FROM   dbo.t1 AS t
WHERE  t.Id > 0
       AND t.Id < 101;

如果它发生在中SUM，您需要转换Amount为BIGINT.

SELECT COUNT(*) AS [Records], SUM(CONVERT(BIGINT, t.Amount)) AS [Total]
FROM   dbo.t1 AS t
WHERE  t.Id > 0
       AND t.Id < 101;

2022-11-11