SQL Server, Division Returns Zero

SQL Server, division returns zero

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);

Division of integers returns 0

You should cast before you divide, but also you were missing a subquery to get the total count from the table. Here's the sample.

select 
  random_int,
  count(random_int) as Count,
  cast(count(random_int) as decimal(7,2)) / cast((select count(random_int) from test) as decimal(7,2)) as Percent
from test
group by random_int
order by random_int;

Division in Sql returning 0 or NULL

You are getting NULL because firstx = 0 and 0 because the rdbms that you use does integer division when dividing firstx/allplaces, so multiply by 1.0 before you divide:

With previous as (
  select Venue, Row,
         Count(case when place = 1 then 1 end) as firstx
         Count(case when place = 2 then 1 end) as secondx
         Count(case when place = 3 then 1 end) as thirdx 
         Count(case when place Between 1 and 15 then 1 end) as allplaces 
  From horses
  Where racetitle not like ‘%stand%’
  Group by venue, row
)
Select case
  When firstx = 0 Then Null
  Else round(1.0 * firstx/allplaces, 2)
End as winratio
From previous
Where allplaces > 10;

I also simplified the CASE expressions inside COUNT() because there is no need for else NULL since this is the default behavior.

How to avoid the divide by zero error in SQL?

In order to avoid a "Division by zero" error we have programmed it like this:

Select Case when divisor=0 then null
Else dividend / divisor
End ,,,

But here is a much nicer way of doing it:

Select dividend / NULLIF(divisor, 0) ...

Now the only problem is to remember the NullIf bit, if I use the "/" key.

Dividing 2 numbers returns 0

I would do it differently, using two sums:

select sum
       ( case
         when endOfUse is null and addDate >= '1/1/2014'
         then 1
         else 0
         end
       )
       * 100.0 -- if you want the usual 0..100 range for percentages
       /
       sum
       ( case
         when addDate >= '1/1/2014'
         then 1
         else 0
         end
       )
       percentage
from   saxref..AuthCycle

Division of two statements with count returns zero

You could simplify your query to one select statement and force casting by multiplication with 100.0 where .0 should do the trick

SELECT
  (SUM(CASE WHEN mass < 575 THEN 1 ELSE 0 END) * 100.0) / COUNT(*)
FROM Male
WHERE location = 'Hawaii'

Division of two values returning 0 rather than positive or negative decimal

Guess you are working on SQL Server. Then change RETURN_ON_SALES to Decimal(10,10) and CAST the inputs or change its data type to DECIMAL

 DECLARE @RETURN_ON_SALES decimal(10,10);
 DECLARE @TW_UNIT_SALES INT ;
 DECLARE @DISTRIBUTION INT ;

 SET @TW_UNIT_SALES=7;
 SET @DISTRIBUTION=41;
 SET @RETURN_ON_SALES=CAST(@TW_UNIT_SALES AS DECIMAL (10,5))/ CAST (@DISTRIBUTION AS DECIMAL(10,5));
 PRINT @RETURN_ON_SALES;

why does sql server return 0 for 1 / 2?

Integer division

select 1 / 2  
-- 0

Float division (at least one argument have to be float/decimal):

select 1 / 2.0
-- 0.5

select 1.0 / 2
-- 0.5

select 1.0 / 2.0
-- 0.5

Divide

If an integer dividend is divided by an integer divisor, the result is
an integer that has any fractional part of the result truncated.

EDIT:

The point is you ask why?
Becasue creator of language decided so, history, convention whatsoever.

I suggest read Is integer division uniquely defined in mathematics?.

Keep in mind that in some languages you have 2 division operators (one for integer division and one for real division).

Division Integer

Dividing integers in a computer program requires special care. Some
programming languages, such as C, treat integer division as in case 5
above, so the answer is an integer. Other languages, such as MATLAB
and every computer algebra system return a rational number as the
answer, as in case 3 above. These languages also provide functions to
get the results of the other cases, either directly or from the result
of case 3.

Names and symbols used for integer division include div, /, \, and %.
Definitions vary regarding integer division when the dividend or the
divisor is negative: rounding may be toward zero (so called
T-division) or toward −∞ (F-division); rarer styles can occur – see
Modulo operation for the details.

---

^{For downvoters leave a comment so I can reply/improve my answer.}

---

Related Topics