Dividing in a case statement and answer is always zero

Question

I'm trying to divide in a case statement. I used nullif to control for the divide by zero error. However, my response is always 0. I'm not sure what I'm doing wrong. Here is the statement:

sum(CASE WHEN aw.goalmet LIKE 'yes' THEN 1 ELSE 0 end)/nullif(sum(case WHEN aw.goalmet IN ('yes', 'no') then 1 else 0 end),0) AS 'Total Passing AIMSWEB2'

Answer 1

You are probably using a database that does integer division (so 1/2 = 0 not 0.5). So, just use a non-integer:

(sum(CASE WHEN aw.goalmet LIKE 'yes' THEN 1.0 ELSE 0 end) /
 nullif(sum(case WHEN aw.goalmet IN ('yes', 'no') then 1 else 0 end), 0)
)

You can simplify this to:

(sum(CASE WHEN aw.goalmet LIKE 'yes' THEN 1.0 ELSE 0 end) /
 sum(case WHEN aw.goalmet IN ('yes', 'no') then 1 end)
)

If nothing matches the denominator, then it will be NULL anyway.

And, if you really want to be fancy and simple:

avg(case when aw.goalmet = 'yes' then 1.0
         when aw.goalmet = 'no'
    end) 

No division required at all.