14

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.

19

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;
  • SQL Server doesn't automatically promote from int to bigint? #TIL So if you're summing that could be bigint, you need to to wrap the value in CONVERT(). Beautiful. – Evan Carroll May 23 '17 at 17:01
  • How did your example on that question work, and mine didn't work then? It's your answer? – Evan Carroll May 23 '17 at 17:03
  • @EvanCarroll I'm not sure! I suppose it's possible that the random value distribution was different and mine skewed low enough to not become a BIGINT. That's about my best guess though. – Erik Darling May 23 '17 at 17:06
  • @EvanCarroll I wrote a dbfiddle after talking about it with Paul W. with a more efficient example, just note that it won't run on dbfiddle because of the partitioning requirements: dbfiddle.uk/… – Erik Darling May 23 '17 at 17:11
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

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.