Dividing 2 numbers returns 0

Question

I'm trying to divide 2 counts in order to return a percentage.

The following query is returning 0:

select (
    (select COUNT(*) from saxref..AuthCycle
         where endOfUse is null and addDate >= '1/1/2014') / 
    (select COUNT(*) from saxref..AuthCycle 
         where addDate >= '1/1/2014')
    ) as Percentage

Should I be applying a cast?

`COUNT(*) * 100` (or `100*(SELECT)`) for the first first value. — Joop Eggen, Oct 13 '14 at 14:01
Syntactically everything looks correct. Can't speak for the data itself obviously. Consider switching to use of CASE statements like in @PatrickHofman's answer below. The execution path will be shorter since only a single read of the table is necessary. — JNevill, Oct 13 '14 at 14:05

Tanner · Answer 1 · 2014-10-14T14:34:16.657

The issue is caused because you are dividing 2 int values, which by default will output an int as it takes the data types used in the calculation to determine the data type of the output, so effectively if you do this:

select 50/100 as result

You get 0.5 output as 0 as it rounds it to an int (no decimal places).

If you however specify decimals:

select 50.0/100.0 as result

You would get 0.5 as a decimal, which you could multiply by 100 to get 50%.

So updating your syntax to multiply by 1.0 and making the counts into decimals would give you the correct result:

select (
(select COUNT(*) from saxref..AuthCycle where endOfUse is null and addDate >= '1/1/2014')*1.0 / 
(select COUNT(*) from saxref..AuthCycle where addDate >= '1/1/2014')*1.0
) as Percentage

score 2 · Answer 2 · answered Oct 13 '14 at 14:22

It can be done more succinctly by moving the common condition to the where clause:

select sum(case when endOfUse is null then 1 end) * 100.0 / count(*) percentage
from saxref..AuthCycle
where addDate >= '1/1/2014'

Note how you don't need the case of 0 for false either, since nulls are ignored with sum()

score 1 · Accepted Answer · answered Oct 13 '14 at 13:59

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

Dividing 2 numbers returns 0

3 Answers3

Linked

Related