Mysql multiple queries into one

Question

I am currently using this query..

SELECT sub_id, pm_id, count(*) AS expect FROM `sub_performancemeasures`
WHERE `progress`='0' GROUP BY pm_id

This is the table generate by the query..

sub_id | pm_id | expect
     1 | 162   | 4
     1 | 163   | 6
     5 | 164   | 2
     1 | 168   | 5

I have other near identical queries...

SELECT sub_id, pm_id, count(*) AS stretch FROM `sub_performancemeasures`
WHERE `progress`='100' GROUP BY pm_id

SELECT sub_id, pm_id, count(*) AS poor FROM `sub_performancemeasures`
WHERE `progress`='-100' GROUP BY pm_id

SELECT sub_id, pm_id, count(*) AS excel FROM `sub_performancemeasures`
WHERE `progress`='200' GROUP BY pm_id

What I want to do is merge all of these into one query where the results look like this..

sub_id | pm_id | poor | expect | stretch | excel
     1 |   162 |    4 |      0 |       2 | 5
     1 |   163 |    6 |      9 |       4 | 1 
     5 |   164 |    2 |      1 |       7 | 9
     1 |   168 |    5 |      3 |       5 | 8

Answer 1

You do not JOIN rather you can do it using the conditional sum as

select
sub_id, 
pm_id,
sum( case when `progress`='0' then 1 else 0 end ) as expect, 
sum( case when `progress`='100' then 1 else 0 end ) as stretch,
sum( case when `progress`='-100' then 1 else 0 end ) as poor,
sum( case when `progress`='200' then 1 else 0 end ) as excel
from `sub_performancemeasures`
group by pm_id

Also note that in standard sql every non aggregate columns should be in group by clause, in mysql however it allows to not to have it, but you should remove the not needed column in the select.