SQL Server, division returns zero
SqlSql Problem Overview
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
Sql Solutions
Solution 1 - Sql
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);
Solution 2 - Sql
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
- declare one or both of the variables as float/double
- 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.
Solution 3 - 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
Solution 4 - Sql
Because it's an integer. You need to declare them as floating point numbers or decimals, or cast to such in the calculation.
Solution 5 - Sql
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
Solution 6 - Sql
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.