SQL Server division returns zero

Question

Here is the code that I use 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 returns 0 instead of 0,073667712

Answer 1

Declare set1 and set2 as float instead of integers, or discard them on float as part of the calculation:

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

Answer 2

When you use only integers in division, you will get integer division. When you use (at least one) double or float, you will get floating point division (and the answer you want to get).

So you can

• declare one or both variables as float / double
• pour one or both variables into float / double.

It’s not easy to convert the result of integer division to double: division has already been performed as integer division, so the numbers behind the decimal point are already lost.

Answer 3

Because it is an integer. You need to declare them as floating point or decimal numbers, or apply them to the calculation.

Answer 4

Just change the bottom of the division to 1.0 (or as many times as you want)

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

Answer 5

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

0

only

For example:

 declare @weight float; SET @weight= 47 / 638; PRINT @weight 

Output: 0

If you want the result to be

0,073667712

For example,

 declare @weight float; SET @weight= 47.000000000 / 638.000000000; PRINT @weight 

Answer 6

In SQL Server, direct separation of two integers returns an integer, even if the result should be a float. The following is an example:

 --1-- declare @weird_number_float float set @weird_number_float=22/7 select @weird_number_float --2-- declare @weird_number_decimal decimal(18,10) set @weird_number_decimal=22/7 select @weird_number_decimal --3-- declare @weird_number_numeric numeric set @weird_number_numeric=22/7 select @weird_number_numeric --Right way declare @weird_number float set @weird_number=cast(22 as float)/cast(7 as float) select @weird_number 

Only the last block will return 3.14285714285714. Despite the fact that the second block is set with the correct accuracy, the result will be equal to 3.00000.