SQL Server – Fixing Arithmetic Overflow Error Converting Expression to Data Type int

aggregatedatatypessql serversql-server-2017

When I run this command with SUM()

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

I'm getting,

Arithmetic overflow error converting expression to data type int.

Any idea on what is the cause of it?

I'm just following the instructions in this answer.

Best Answer

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;
Related Question