SQL Server error 8115: 'Arithmetic overflow error converting expression to data type int'

Arithmetic overflow error converting expression to data type int. (error 8115) means a result went past the int limit of 2,147,483,647. A SUM of an int column is itself an int, and so is int * int. Cast to bigint before adding or multiplying, and the same query returns the right total.

Write SUM(CAST(Amount AS bigint)) instead of SUM(Amount), and CAST(Amount AS bigint) * 2 instead of Amount * 2. The cast has to happen before the arithmetic, not on its result.

The error

SELECT SUM(Amount) AS Total FROM dbo.Transfers;

Msg 8115, Level 16, State 2, Server MACHINE\LOCALDB#EAD42A01, Line 2
Arithmetic overflow error converting expression to data type int.

A multiplication gave the same message:

SELECT Amount * 2 AS Doubled FROM dbo.Transfers WHERE Id = 1;

Msg 8115, Level 16, State 2, Server MACHINE\LOCALDB#EAD42A01, Line 2
Arithmetic overflow error converting expression to data type int.

Why it happens

SQL Server picks the type of an expression from the types going in. Amount is an int, so SUM(Amount) is an int and Amount * 2 is an int. The table held three rows, two of 1,500,000,000 and one of 200. Each value fits an int comfortably, but their sum, 3,000,000,200, does not, and neither does 1,500,000,000 times two. SQL Server does not widen the type automatically; it stops the statement.

This is why the error tends to appear long after the code was written: every row is valid, and the query works until the table grows or the values get large enough that the total crosses the limit.

The fix

SELECT SUM(CAST(Amount AS bigint)) AS Total FROM dbo.Transfers;
SELECT CAST(Amount AS bigint) * 2 AS Doubled FROM dbo.Transfers WHERE Id = 1;

The two queries returned 3000000200 and 3000000000. Casting one operand is enough: the whole expression is then computed as bigint. Casting the result instead, CAST(SUM(Amount) AS bigint), does not help: it failed with the same error 8115, because the overflow happens inside SUM before the cast is reached. When the column itself will hold large values, change it to bigint in the table so every query is safe. For counting rows the same thinking applies: SQL_VARIANT_PROPERTY reported COUNT(*) as int and COUNT_BIG(*) as bigint.

How it was reproduced

One script run with sqlcmd -S "(localdb)\MSSQLLocalDB" -d fix8_lab -I -i e8115.sql -W on SQL Server 2022 LocalDB 16.0.1210.3 and sqlcmd 16.0.1000.6, in a throwaway database fix8_lab created for the test and dropped afterwards. A transfers table with an int amount column and three rows. The server's computer name in the messages is replaced by MACHINE; nothing else changed.

Frequently asked

Why does SUM give 'Arithmetic overflow error converting expression to data type int'?

SUM of an int column returns an int. When the total passes 2,147,483,647, SQL Server raises error 8115 even though every single row fits in an int.

How do I fix arithmetic overflow in SUM in SQL Server?

Cast inside the aggregate: SUM(CAST(Amount AS bigint)). Casting the result of SUM is too late, because the overflow happens while the sum is computed.

Does multiplying two int columns overflow in SQL Server?

Yes, int times int is computed as int. In the test, 1,500,000,000 times 2 failed with error 8115; CAST(Amount AS bigint) * 2 returned 3000000000.

More decoded errors in the Fixes category.