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
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
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
This produces the results for Diploma Only as follows
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.
Advertisement
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
Any improvement toward my approach please welcome.