How to calculate frequency percentages for a table using SQL?

Question

Let's say I have some data as follows:

 ID   data  fingers  rating
001  hello        y       0
002  hello        n       0
003    bye        n       0
004  hello        y       1
005    bye        n       0

All I want is a table that shows the freqency of each value in data

Resulting as follows:

 data  count  pct
hello      3   .6
  bye      2   .4

Feels simple but I'm struggling to make this work in Snowflake, any ideas?

Answer 1

here is how you can do it:

select data , count(*) count, count(*) / sum(count(*)) over () pct
from table
group by data;

Answer 2

You can use conditional aggregation:

select data, count(*),
       avg(case when fingers = 'y' then 1.0 else 0 end)
from t
group by data;