so here is what I have:
select round(count(hired) / sum(count(population)) over(), 2) as freq, hired, population from hr group by hired, population order by population, hired DESC;
I have a hired column which is boolean values, population which is either US or EU values. Right now the freq divides the hired over the entire population which is like 5,000 values but I would like it to divide which are specifically US or EU values.
Can anyone help me with doing this? thanks
Example data:
create table hr ( candidate_id INTEGER PRIMARY KEY, hired BOOLEAN NOT NULL, population CHAR(2) NOT NULL ); INSERT INTO hr VALUES (1,FALSE,'US'); INSERT INTO hr VALUES (2,TRUE,'US'); INSERT INTO hr VALUES (3,FALSE,'EU'); INSERT INTO hr VALUES (4,TRUE,'EU'); INSERT INTO hr VALUES (5,FALSE,'US'); INSERT INTO hr VALUES (6,FALSE,'EU');
instead of dividing by the entire population I just want by like total of EU or US values
so like True in EU is 1 but total for EU is 3 so 1/3 instead of 1/6
Sample output:
/* Result: frequency | hired |population| ----------+---------+----------+ 0.33 | True | US | 0.66 | False| US | 0.33 | True | EU | 0.66 | False| EU | */
Advertisement
Answer
select round(cnt/sum(cnt) over (partition by population), 2) as frequency, hired, population from ( select population, hired, count(*)::numeric cnt from hr group by population, hired ) t order by population desc, hired desc; /* Result: frequency|hired|population| ---------+-----+----------+ 0.33|true |US | 0.67|false|US | 0.25|true |EU | 0.75|false|EU | */