SQL Server returns "Arithmetic overflow error converting expression to data type int."
For values larger than the INT
max (2,147,483,647), you'll want to use 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;
If it's happening in the SUM
, you need to convert Amount
to a 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;
This issue is caused by SUM()
function
you have to CAST t.Amount
as BIGINT
SELECT COUNT(*) AS [Records], SUM(CAST(t.Amount AS BIGINT)) AS [Total]
FROM dbo.t1 AS t
WHERE t.Id > 0
AND t.Id < 101;
Reference
- https://stackoverflow.com/questions/8289310/how-to-prevent-arithmetic-overflow-error-when-using-sum-on-int-column