Snowflake Count Group By With Condition

Question

I need to measure the ratio of "Total Query Fails per Object / Total Query Count per Object"

My data looks like the below.

query_id	object	status
1	a	success
2	a	fail
3	a	fail
4	b	success
5	b	fail

I'm able to use the below to measure total fails per object / total fails e.g.

select
object
count(distinct query_id) as num_distinct_queries
ratio_to_report(count(distinct query_id) over () as percent_of_total_fails
from my_table
where status='fail'
group by object
order by 3

object	num_distinct_queries	percent_of_total_fails
a	2	0.666666
b	1	0.333333

I want to achieve something like:

object	num_distinct_queries	num_fails	percent_of_fails_per_table
a	3	2	0.666666
b	2	1	0.5

Any advice how to achieve the second option? Total fails per object / Total queries per object.

Answer 1

Using COUNT_IF to perform conditional aggregation:

select object,
  count(distinct query_id) as num_distinct_queries,
  count_if(status='fail') AS num_fails,
  count_if(status='fail')/count(*) as percent_of_total_fails
from my_table
group by object
order by 3;

Output:

db<>fiddle demo