SQL Server Error: Arithmetic overflow error converting int to data type numeric

Last Reply 6 months ago By dharmendr

Posted 6 months ago

I have a following table tblProducts

ID

Size

Qty

1

40

100

2

45

100

3

50

100

i want to get output like this

Required Output

ID

BSize

Qty

Total (Qty / Size)

1

40

100

100/40 = 2.5

2

45

100

100/45 = 2.22

3

50

100

100/50 = 2

 and my query is getting error

select s.ProductID,P_Name,BSize,Quantity,
CAST(CAST(Quantity AS DECIMAL(2,0))/CAST(BSize AS DECIMAL(4,0)) AS DECIMAL(10,3))
from tblPurchase as s 

it is showing me an error

Msg 8115, Level 16, State 8, Line 9 Arithmetic overflow error converting int to data type numeric.

how to get solution pls?

You are viewing reply posted by: dharmendr 6 months ago.
Posted 6 months ago
smile says:
CAST(Quantity AS DECIMAL(2,0))

The error is due to Decimal declaration.

Your Qty field is having length of 3 i.e. 100. But you are trying to CAST with 2.

Change the size of the DECIMAL.

Check the below query.

SQL

DECLARE @Test AS TABLE(ID INT,Size INT,Qty INT)
INSERT INTO @Test VALUES(1,40,100)
INSERT INTO @Test VALUES(2,45,100)
INSERT INTO @Test VALUES(3,50,100)
SELECT ID,Size,Qty,
CAST(CAST(Qty AS DECIMAL(3,0))/CAST(Size AS DECIMAL(4,0)) AS DECIMAL(10,2)) Total
FROM @Test

Output

ID  Size  Qty    Total

1    40    100    2.50

2    45    100    2.22

3    50    100    2.00