How can avoid arithmetic overflow in sql server if I dont have a column destiny?

Question

I have this query:

SELECT 
  client, 
  month1,
  month2,
  month3,
  month4,
  month5,
  month6,
  (isnull(month1, 0) +
   isnull(month2, 0) +
   isnull(month3, 0) +
   isnull(month4, 0) +
   isnull(month5, 0) +
   isnull(month6, 0)) /6 as prom
FROM client_total_sales

Months can have null values.

I tried with round, converting data types, but the problem persist.

SQL Server Management Studio show the error during load of the query, I mean a see a few results before the error comes out.

Error message: Mens. 8115, Level 16, State 2, Line 1 Error de desbordamiento aritmético (arithmetic overflow error) al convertir expression al tipo de datos int.

What are you asking? How to avoid SSMS crash? Have you tried multiple times? How many rows should the result output? What have you read about SSMS crashing by now? In the meantime, look here, maybe it helps. — OzrenTkalcecKrznaric

jpw jpw · Accepted Answer · 2013-07-18T22:31:56

If the error is Arithmetic Overflow in the calculated column promyou can force a promotion to a data type that can hold more data by casting one of the parameters of the calculation like this:

SELECT 
  client, month1, month2, month3, month4, month5, month6,
  (
   cast(isnull(month1, 0) as bigint) +
   isnull(month2, 0) + isnull(month3, 0) +
   isnull(month4, 0) + isnull(month5, 0) +
   isnull(month6, 0)) /6 as prom
FROM client_total_sales

Not knowing what data type monthX is I choose BIGINT for the example. SQL Server doesn't promote to BIGINT automatically so you have to do it yourself.

How can avoid arithmetic overflow in sql server if I dont have a column destiny?

1 Answers