CODEWITHSUNDEEP
sql
Jon
Jon

Reputation: 403

Calculating percentage within a group

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)

Upvotes: 40

Views: 53256

Answers (3)

Azmisov
Azmisov

Reputation: 7269

Many DB's support RATIO_TO_REPORT windowing function, which I think is more readable:

select sex, employed, ratio_to_report(count) over(partition by sex) as percent
    from (select sex, employed, count(*) as count from my_table group by sex, employed);

I haven't benchmarked speed compared to the other answers here

Upvotes: 0

Jan Pravda
Jan Pravda

Reputation: 771

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

Upvotes: 77

outis
outis

Reputation: 77450

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.

Upvotes: 16

Related Questions