asked on

Arithmetic overflow error converting expression to data type int.

I have a select statement which has some calculated column, which fails with the following error

Arithmetic overflow error converting expression to data type int.

and here is the calculated column
convert(int, sum(abs(Quantity * Price))) as TotalValue

Price is a column defines as Decimal(20,6)
Quantity is a column defined as decimal(20,6)

I thought if my field is going to be in the range of -2,147,483,648 to 2,147,483,647, you can declare it with the int keyword as data type.

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kevin Cross

I agree with chap. int is 4 bytes and product of your two decimals could be exceeding that value. It may be possible that the system thinks it will exceed due to size of decimal(20, 6) declaration versus actual data but running query above will show you what is going on for sure.

http://msdn.microsoft.com/en-us/library/ms173773.aspx

You could use float or money to limit size of datatype to something inline with int/bigint.

SOLUTION

Schunk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

countrymeister

ASKER

I tried

select
sum(abs(Quantity * Price)) as TotalValue
and still get the arithmetic error

chapmandew

try this:

select
sum(abs(cast(Quantity as decimal(50, 20))* cast(Price as decimal(50,20)))) as TotalValue

countrymeister

ASKER

The highest value in Quantity is 88872076
and the corresponding Price is 11694794

so if I multiply that would result into 978619905823034

chapmandew

select
cast(abs(cast(Quantity as bigint)* cast(Price as bigint))) as bigint)
from tablename

Kevin Cross

Definitely bigger than an INT. :)
Should fit in a BIGINT though.