Calculating percentage within a group

Question

given a table that for the following commands:

select sex, count(*) from my_table group by sex; select sex, employed, count(*) from my_table group by sex, employed; 

gives:

  sex  | count  -------+------ male   | 1960  female | 1801 

and:

 sex     | employed | count  ---------+----------+-------  male    | f        |  1523   male    | t        |   437   female  | f        |  1491   female  | t        |   310  

I'm having a difficulty writing a query that will calculate percentage of employed within each sex group. So the output should look like this:

 sex     | employed | count  | percent ---------+----------+--------+-----------  male    | f        |  1523  | 77.7% (1523/1960)  male    | t        |   437  | 22.3% (437/1960)  female  | f        |  1491  | 82.8% (1491/1801)  female  | t        |   310  | 17.2% (310/1801) 

Answer 1

May be too late, but for upcoming searchers, possible solution could be:

select sex, employed, COUNT(*) / CAST( SUM(count(*)) over (partition by sex) as float)   from my_table  group by sex, employed 

By IO Statistics this seems to be most effective solution - may be dependant on number of rows to be queried - tested on numbers above ...

The same attitude could be used for getting male / female percentage:

select sex, COUNT(*) / CAST( SUM(count(*)) over () as float)   from my_table  group by sex 

Regards, Jan

Answer 2

You can do it with a sub-select and a join:

SELECT t1.sex, employed, count(*) AS `count`, count(*) / t2.total AS percent   FROM my_table AS t1   JOIN (     SELECT sex, count(*) AS total        FROM my_table       GROUP BY sex   ) AS t2   ON t1.sex = t2.sex   GROUP BY t1.sex, employed; 

I can't think of other approaches off the top of my head.