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)
CodePudding user response：
Count conditionally and divide by the total count:
select category, count_if(boolean) / count(*)
from mytable
group by category
order by category;