# question

asked

## Cast Error

I am trying to divide two columns and the result keeps coming back as 0.0000 when that isn’t the correct answer. I believe it is a cast error If I add the columns it produces the correct result the problem occurs when I divide. select colA, ColB, ((ColA/ColB)* 100.0000) AS Answer from tableA ColA ColB Answer 1 2 0.0000
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
0 Likes 0 ·

### 2 Answers

·
Yes it is a cast/datatype 'error' You are doing **integer** division so 1/2 = 0 is the right answer If you cast the first column as a decimal, ensuring that you give enough precision for your data for example.... select cast(colA as decimal(8,2))/ColB * 100
2 comments

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
@KevRiley I have tried that solution and keep getting an arithmetic overflow error.
0 Likes 0 ·
·
@sqlnewb You can try to either increase the precision of the decimal or else use the ((colA\*1.0)/colB)\*100
0 Likes 0 ·
This is because when an integer division takes place, 1/2 = 0. Once this happens, you then multiply it by 100.0000 but this is already too late. To prevent the integer division from happening, you can either cast colA or colB as a sutable decimal or multiply any one of them by 1.0 to get the same result, i.e. select colA, ColB, (cast(ColA as decimal(18,4))/ColB)* 100.0000) AS Answer from tableA; -- this should work as well select colA, ColB, (((ColA*1.0)/ColB)* 100.0000) AS Answer from tableA; Oleg
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
+1 A better solution!
0 Likes 0 ·

### Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.