SQL Server returns “Arithmetic overflow error converting expression to data type int.”
evan carroll (imported from SE) 
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](https://dba.stackexchange.com/a/174093/2639).
Top Answer
meme (imported from SE) 
For values larger than the `INT` max (2,147,483,647), you'll want to use [COUNT_BIG][1](*).

    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;


  [1]: https://docs.microsoft.com/en-us/sql/t-sql/functions/count-big-transact-sql
comments
transcript

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.