SQL Server, division returns zero

Question

Here is the code I'm using in the example:

 PRINT @set1
 PRINT @set2

 SET @weight= @set1 / @set2;
 PRINT @weight

Here is the result:

47
638
0

I would like to know why it's returning 0 instead of 0,073667712

Answer 1

Because it's an integer. You need to declare them as floating point numbers or decimals, or cast to such in the calculation.

Answer 2

if you declare it as float or any decimal format it will display

0

only

E.g :

declare @weight float;

SET @weight= 47 / 638; PRINT @weight

Output : 0

If you want the output as

0.073667712

E.g

declare @weight float;

SET @weight= 47.000000000 / 638.000000000; PRINT @weight

Answer 3

Either declare set1 and set2 as floats instead of integers or cast them to floats as part of the calculation:

SET @weight= CAST(@set1 AS float) / CAST(@set2 AS float);

Answer 4

When you use integers in a division, you will get integer division. When you use doubles/floats, you will get floating point division (and the answer you want to get).

So you can

1. declare one or both of the variables as float/double
2. cast one or both of the variables to float/double.

Do not just cast the result of the integer division to double: the division was already performed as integer division, so the numbers behind the decimal are already lost.

Answer 5

Simply mutiply the bottom of the division by 1.0 (or as many decimal places as you want)

PRINT @set1 
PRINT @set2 
SET @weight= @set1 / @set2 *1.00000; 
PRINT @weight