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

@bub I think you are going to need more than just a join to satify this requirement — RiggsFolly
– RiggsFolly, Commented Aug 26, 2015 at 14:48

Abhik Chakraborty · Accepted Answer · 2015-08-26 14:53:41Z

4

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.

answered Aug 26, 2015 at 14:53

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jampez77 Over a year ago

DING DING DING! We have a winner. Thank you. Work perfectly. :)

Maximus2012 · Accepted Answer · 2015-08-26 14:51:34Z

Try this (LEFT JOIN on a derived table). This works for expect and stretch which you can then easily extend for poor and excel (or even more columns in the future):

SELECT sub.sub_id, sub.pm_id, CASE WHEN 
temp_expect.expect IS NOT NULL THEN temp_expect.expect ELSE '0' END AS expect,
CASE WHEN temp_stretch.stretch IS NOT NULL 
THEN temp_stretch.stretch ELSE '0' END AS stretch
FROM sub_performancemeasures sub
LEFT JOIN
(
    SELECT pm_id, count(*) AS expect FROM `sub_performancemeasures`
    WHERE `progress`='0' GROUP BY pm_id
)
AS temp_expect ON sub.pm_id = temp_expect.pm_id
LEFT JOIN
(
    SELECT pm_id, count(*) AS stretch FROM `sub_performancemeasures`
    WHERE `progress`='100' GROUP BY pm_id
)
AS temp_stretch ON sub.pm_id = temp_stretch.pm_id

Ankit Bajpai · Accepted Answer · 2015-08-26 14:54:58Z

0

You can try following query:-

SELECT sub_id, pm_id, COUNT( CASE WHEN `progress`='0' THEN * ELSE NULL END )AS expect,
                      COUNT( CASE WHEN `progress`='100' THEN * ELSE NULL END )AS stretch,
                      COUNT( CASE WHEN `progress`='-100' THEN * ELSE NULL END )AS poor,
                      COUNT( CASE WHEN `progress`='200' THEN * ELSE NULL END )AS excel 
FROM `sub_performancemeasures`
GROUP BY pm_id

answered Aug 26, 2015 at 14:54

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

Collectives™ on Stack Overflow

Mysql multiple queries into one

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related