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