How to get percentage based on columns-CodePudding

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 |
*/

CodePudding user response：

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        |
*/