CODEWITHSUNDEEP
sqlgroup-bysnowflake-cloud-data-platformfrequency
John Thomas
John Thomas

Reputation: 1105

How to calculate frequency percentages for a table using SQL?

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?

Upvotes: 2

Views: 1306

Answers (2)

eshirvana
eshirvana

Reputation: 24568

here is how you can do it:

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

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use conditional aggregation:

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

Upvotes: 0

Related Questions