Get the percentage true of a boolean column in Snowflake SQL

Is there a function in Snowflake SQL to get the percentage of a boolean column?

For instance, if my data were to look like this:

id category boolean
1 a true
2 a false

I would like to use something like RATIO_TO_REPORT() to get:

category pct
a 0.50

One thought is to create a numerical column from boolean in a CTE, then use it to generate a pct, however, I have a lot of data, so am wondering if there is a faster way (i.e. some native sql function or approach I am missing).

Additionally, I would like to solve for categories with more than 1 grouping column (e.g. category2) and more than 1 boolean column (e.g. boolean2)

>Solution :

Count conditionally and divide by the total count:

select category, count_if(boolean) / count(*)
from mytable
group by category
order by category;

Leave a Reply