0

I am getting this error in SQL Server:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

when running this query:

SUM(CASE WHEN t.type = 'doc' THEN t.id ELSE 0 END) AS DocType,
SUM(CASE WHEN t.type = 'pdf' THEN t.id ELSE 0 END) AS PdfType

Type is varchar and id is int.

I tried with SUM(CASE WHEN t.type = 'doc' THEN ISNULL(t.id, 0) ELSE 0 END) AS DocType

but that's not working.

What am I missing ?

Improve this question
4
  • 4
    SUM returns the data type of the input expression, which in this case appears to be a int. perhaps the sum of your column ID (seems odd you're summing the value of your ID's) is greater than 2,147,483,647. Try CASE WHEN t.type='doc' THEN CONVERT(bigint,t.id) ELSE 0 END Does that work? Commented Mar 25, 2019 at 9:22
  • 1
    As @Larnu said, it seems odd that you're summing Id values, especially as you're aliasing the result of the sum to DocType and PdfType - What's logic behind your query? Are you trying count the number of records of type Doc and Pdf? Commented Mar 25, 2019 at 9:24
  • 3
    Maybe what you really want is COUNT? Commented Mar 25, 2019 at 9:26
  • Possible duplicate of SQL Server : Arithmetic overflow error converting expression to data type int Commented Mar 25, 2019 at 9:32
Related questions
3
Arithmetic overflow error converting expression to data type int
1
"Arithmetic overflow error converting expression to data type int"
27
How to prevent arithmetic overflow error when using SUM on INT column?
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.