25

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.

32

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;
| improve this answer | |
11

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.