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
This question is related to
sql
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
Because it's an integer. You need to declare them as floating point numbers or decimals, or cast to such in the calculation.
When you use only integers in a 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
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.
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
In SQL Server direct division of two integer returns integer even if the result should be the float. There is an example below to get it across:
--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
Just last block will return the 3,14285714285714. In spite of the second block defined with right precision the result will be 3.00000.
Source: Stackoverflow.com