Count Males and Females Based on Categories and Group

members of Stackoverflow,I have been working with Mysql php programing for nine months ,but I have stuck while developing a small employee platform.I know it might be a duplicate question but I have been searching how to come across this without any hope
Here is my Mysql table with employees

No  full_name       gender          proffesional      Center
1  Ali P Ali            M           BACHELOR            JUIO
2. PETER PETERS         M           DIPLOMA             PETERS
3. ASHA AL ASHA         F           BACHELOR            ST.JOHN
4. JAMAL ALI JAMAL      M           DIPLOMA             ST.JOHN
5.JULIO J. JULIO        F           DIPLOMA             ST.JOHN
6.ROSE A LYMO           F           BACHELOR            PETERS

JavaScript
​x
 
No  full_name       gender          proffesional      Center1  Ali P Ali            M           BACHELOR            JUIO2. PETER PETERS         M           DIPLOMA             PETERS3. ASHA AL ASHA         F           BACHELOR            ST.JOHN4. JAMAL ALI JAMAL      M           DIPLOMA             ST.JOHN5.JULIO J. JULIO        F           DIPLOMA             ST.JOHN6.ROSE A LYMO           F           BACHELOR            PETERS​

I want to query this table to produce the following results. How do I come across this????

 center Name     |BACHELOR  | DIPLOMA   | Total
                 |M  | F    | M   | F    | 
   JUIO          | 1 | 0    | 0   |0     |  1
   PETERS        |   | 1    | 1   |0     |  2
   ST.JOHN       |   | 1    | 1   |1     |  3
  TOTAL          | 1 | 2    | 2   |1     |  6

JavaScript
 
 center Name     |BACHELOR  | DIPLOMA   | Total                 |M  | F    | M   | F    |    JUIO          | 1 | 0    | 0   |0     |  1   PETERS        |   | 1    | 1   |0     |  2   ST.JOHN       |   | 1    | 1   |1     |  3  TOTAL          | 1 | 2    | 2   |1     |  6​

This is what I have tried so far

select employee.work_center,    count(case when gender='M' then 1 end)as male,  count(case when gender='F' then 1 end) as female   from employee WHERE proffesional="DIPLOMA" group by work_center

JavaScript
 
select employee.work_center,    count(case when gender='M' then 1 end)as male,  count(case when gender='F' then 1 end) as female   from employee WHERE proffesional="DIPLOMA" group by work_center​

This produces the results for Diploma Only as follows

 work_center male female    
    QUET        0   1
    ST.JOHN     2   0
    ST.PETERS   1   1

JavaScript
 
 work_center male female        QUET        0   1    ST.JOHN     2   0    ST.PETERS   1   1​

The script above gives a result in total and not showing for each center.

Answer

After working for couple of hours I have managed to write the following query and working well.

select work_center,
count(case when proffesional="DIPLOMA" AND gender='M' then 1 end)as DP_male,
count(case when proffesional="DIPLOMA" AND gender='F' then 1 end) as DP_female,
count(case when proffesional="BACHELOR" AND gender='M' then 1 end)as BAC_male,
count(case when proffesional="BACHELOR" AND gender='F' then 1 end) as BAC_female from employee group by work_center

JavaScript
 
select work_center,count(case when proffesional="DIPLOMA" AND gender='M' then 1 end)as DP_male,count(case when proffesional="DIPLOMA" AND gender='F' then 1 end) as DP_female,count(case when proffesional="BACHELOR" AND gender='M' then 1 end)as BAC_male,count(case when proffesional="BACHELOR" AND gender='F' then 1 end) as BAC_female from employee group by work_center​

Any improvement toward my approach please welcome.

Advertisement

Answer