CODEWITHSUNDEEP
sqlpostgresqlgroup-byfrequency
Mobix
Mobix

Reputation: 103

Postgresql frequency table with percentage partition over and group by

I'm trying to create a frequency table with percentage in Postgresql

If anyone is familiar with SAS i'm trying to recreate a proc frequency table

Below I'm trying to get the frequency with a group by on var1,var2

var1     var2    frequency  percentage 
A        20       1          33%
A        30       1          33%
A        40       1          33%
B        20       4          80%
B        30       1          20%

Now that is easy with just

select var1
,var2
,count(*)
from table
group by 1,2

What gets tricky is where I try to add a percentage column which does % based on var1 distribution

select var1
,var2
,count(*)
,count(*)/count(*) over(partition by var1)
from table
group by 1,2

I get a wrong answer with the code above

Upvotes: 0

Views: 1254

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You want to sum the count(*) values. So:

select var1, var2, count(*),
       count(*) * 1.0 / sum(count(*)) over (partition by var1)
from table
group by 1, 2;

Your code just counts the number of rows for each var after the aggregation. Hence, it is actually returning the weighted average -- something that might be useful but not what you want.

Upvotes: 1

Related Questions