Arithmetic overflow depending on group by columns

Question

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:

AtgardAvvattningId	ObjektId	TotalLangd
DC9...	9B2...	25684
ECD...	9B2...	25700
3D0...	9B2...	170005
959...	9B2...	170005
BEC...	214...	11814
C31...	214...	11815

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:

AtgardAvvattningId	ObjektId	PositionId	VnaId	StartLopLangd	SlutLopLangd	Subtraction
DC96...	9B2...	473...	1345183	168501	174922	6421
ECD4...	9B2...	07E...	1252649	74602	81027	6425
ECD4...	9B2...	07E...	1252651	74602	81027	6425
ECD4...	9B2...	07E...	1252652	74602	81027	6425
ECD4...	9B2...	07E...	1252650	74602	81027	6425
DC96...	9B2...	473...	1345180	168501	174922	6421
DC96...	9B2...	473...	1345181	168501	174922	6421
DC96...	9B2...	473...	1345182	168501	174922	6421
3D08...	9BC...	F18...	1374284	199000	233001	34001
3D08...	9BC...	F18...	1374283	199000	233001	34001
9590...	9BC...	A2D...	1374285	16591	50592	34001
9590...	9BC...	A2D...	1374286	16591	50592	34001
9590...	9BC...	A2D...	1374287	16591	50592	34001
9590...	9BC...	A2D...	1374289	16591	50592	34001
9590...	9BC...	A2D...	1374288	16591	50592	34001
3D08...	9BC...	F18...	1374281	199000	233001	34001
3D08...	9BC...	F18...	1374280	199000	233001	34001
3D08...	9BC...	F18...	1374282	199000	233001	34001
C31B...	214...	B20...	1349999	32756	44571	11815
BEC3...	214...	F21...	1349998	205022	216836	11814

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

Answer 1

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);