I'm getting this error but only when grouping by specific columns:

Arithmetic overflow error converting expression to data type int.

And I can't wrap my head around why. This is the query causing it (the sum-function is the culprit):

SELECT  a.AtgardAvvattningId, 
        a.ObjektId,
        sum(p.SlutLopLangd - p.StartLopLangd) As TotalLangd
    FROM AtgardAvvattning a 
        INNER JOIN Objekt o ON o.ObjektId = a.ObjektId 
        INNER JOIN Position p ON p.AvvattningAtgardId = a.AtgardAvvattningId 
        INNER JOIN Vna v ON v.PositionId = p.PositionId 
    WHERE v.OID IN (...) 
    GROUP BY a.AtgardAvvattningId, a.ObjektId, o.AtgardsDatum
    ORDER BY a.ObjektId

p.SlutLopLangd and p.StartLopLangd are both int columns. If I convert the values to bigints before sumation it works:

    sum(CONVERT(bigint, p.SlutLopLangd - p.StartLopLangd)) As TotalLangd

Giving this result:

As you can see, no sum is even near the limit for int. The wierd thing is if I include the positionId in the group by clause like this it doesn't raise an error:

SELECT  a.AtgardAvvattningId, 
        a.ObjektId,
        sum(p.SlutLopLangd - p.StartLopLangd) As TotalLangd
    FROM AtgardAvvattning a 
        INNER JOIN Objekt o ON o.ObjektId = a.ObjektId 
        INNER JOIN Position p ON p.AvvattningAtgardId = a.AtgardAvvattningId 
        INNER JOIN Vna v ON v.PositionId = p.PositionId 
    WHERE v.OID IN (...) 
    GROUP BY a.AtgardAvvattningId, a.ObjektId, o.AtgardsDatum, p.PositionId
    ORDER BY a.ObjektId

In this case it's a 1-to-1 relationship between AtgardAvvattning and Position. This query gives the exact same result as above.

Why is it raising an Arithmetic overflow in the first place when the values are so small? And why does it work in the second? What's different? I know it's probably hard to give an answer without data and table structures but any hint would be helpful.

Update:

When removing the group by completly with this query:

    SELECT  a.AtgardAvvattningId, 
            a.ObjektId,
            p.PositionId,
            v.VnaId,
            p.StartLopLangd,
            p.SlutLopLangd,
            p.SlutLopLangd - p.StartLopLangd as Subtraction
        FROM AtgardAvvattning a 
            INNER JOIN Objekt o ON o.ObjektId = a.ObjektId 
            INNER JOIN Position p ON p.AvvattningAtgardId = a.AtgardAvvattningId 
            INNER JOIN Vna v WITH (NOLOCK) ON v.PositionId = p.PositionId 
        WHERE v.OID IN (...) 
        ORDER BY a.ObjektId

The result is not many rows at all:

And however you sum the rows it should be hard to reach the int overflow limit.

CodePudding user response：

The final value doesn't actually matter. What is likely happening, is that at some point in your SUM you are going over the maximum value (2,147,483,647) or minimum value (-2,147,483,648) for an int and getting the error.

Take this example:

SELECT SUM(V.I)
FROM (VALUES(2147483646),
            (2),
            (-2006543543))V(I);

This will likely generate the same error:

Arithmetic overflow error converting expression to data type int.

The result of the SUM however, would be 140,940,105 (well below the maximum). This is because if 2147483646 and 2 are summed first, then you get 2147483648, which is larger than the maximum value of an int. If you CAST/CONVERT the value first, you don't get the error:

SELECT SUM(CONVERT(bigint,V.I))
FROM (VALUES(2147483646),
            (2),
            (-2006543543))V(I);