Dividing counts of rows with different categories in Impala

Question

Hi I have table like this and would like to construct views for getting the rate of sums(counts) for each type.
For each type, I would like to divide the sum of counts with NULL in "cat" and sum of counts with non-null value in "cat"

This is my query which give me incorrect results (I think because I need to sum the counts first, but Im struggling to do so)

select case   
    When protocol = 61002 AND type= 3 THEN   "S11 Success Rate" 
    When protocol= 61002 AND type = 4 THEN   "S11 Bearer Success Rate"  
end as name,  
max(case when cat is null then count end ) / nullif(max(case when cat is not null then count end),0) as result  
from table

using this table:

The desired result for type 3 would be result=(63+15456) / (51609+18127)=0.2225

Answer 1

I feel like this is what you are looking for:

select 
case
    When protocol = 61002 AND type = 3 THEN "S11 Success Rate" 
    When protocol = 61002 AND type = 4 THEN "S11 Bearer Success Rate"
end as name,
sum(case when cat is null then count else 0 end)/sum(case when cat is null then 0 else count end)
from table
group by name

This produces the following result:

S11 Success Rate 0.22253929104049558

S11 Bearer Success Rate 0.90956749672346004

Grouping makes sure you can aggregate by type. The case statement sets the value to 0 when it is not in the correct case. This results in the correct sum you expected.