locked
Arithmetic overflow error converting expression to data type int. RRS feed

  • Question

  • I am running the following Query

    select

     

    SUM(col1) from table1 and i got the following error Arithmetic overflow error converting expression to data type int.

    The datatype of this column is int and the records in the column has negative values (-6764242, -86427987879 etc) .

    I want the summation of this column to be placed in an audit table during an ssis package runtime . Help me out in this regard.

    Wednesday, February 17, 2010 6:54 PM

Answers

  • Do the following

    SELECT SUM(CAST(col1 AS BIGINT))
    FROM TableName

    Abdallah El-Chal, PMP, ITIL, MCTS
    Wednesday, February 17, 2010 7:28 PM

All replies

  • Probably the result of the SUM doesn't fit for an int because that's the datatype used when you add integers. Here is an example and how to solve it
    DECLARE @i INT, @j INT
    SET @i = 2147483647
    SET @j = 2147483647
    
    --This will produce an error
    SELECT SUM(@i + @j)
    
    --This will work fine
    SELECT SUM(CAST(@i AS BIGINT) + CAST(@j AS BIGINT))




    Abdallah El-Chal, PMP, ITIL, MCTS
    • Proposed as answer by Brad_Schulz Wednesday, February 17, 2010 8:32 PM
    Wednesday, February 17, 2010 6:59 PM
  • How can i use the above condition for a single column

    select sum(cast(col1)) from tablename
    Wednesday, February 17, 2010 7:22 PM
  • Do the following

    SELECT SUM(CAST(col1 AS BIGINT))
    FROM TableName

    Abdallah El-Chal, PMP, ITIL, MCTS
    Wednesday, February 17, 2010 7:28 PM