How to Combine columns from multiple query results in mySQL?

Question

I need to combine the columns resulting from running two different queries in mySQL.

Query A :

SELECT us.id as RecruiterID,
       concat(us.first_name," ",us.last_name) as Recruiter,
       count(aj.current_step>6) as Submissions 
FROM applied_jobs as aj 
LEFT JOIN job_postings as jp ON aj.job_posting_id=jp.id 
LEFT JOIN users as us ON aj.applied_by_id=us.id 
WHERE aj.created_at BETWEEN CURDATE() AND CURDATE()+1 
AND aj.current_step>6
group by Recruiter

Query B :

SELECT concat(us.first_name," ",us.last_name) as Recruiter,
       COUNT(sc.applied_jobs_id) as SELECTIONS
FROM `selected_candidates` as sc 
LEFT JOIN applied_jobs as aj ON sc.applied_jobs_id=aj.id 
LEFT JOIN users as us ON aj.applied_by_id=us.id 
WHERE sc.`created_at` BETWEEN CURDATE() AND CURDATE()+1
GROUP BY Recruiter

Ergest Basha · Accepted Answer · 2022-02-07 16:21:38Z

Even though your question needs a bit of clarity , you could use UNION ALL and in an outer query do the select and add aggregate function SUM in your case grouping by Recruiter. Note that I added null as selections and null as submissions.

Try:

select t1.Recruiter,
       sum(t1.Submissions) as submissions,
       sum(t1.selections) as selections
from ( 

         SELECT 
                concat(us.first_name," ",us.last_name) as Recruiter,
                count(aj.current_step>6) as submissions,
                null as selections     
         FROM applied_jobs as aj 
         LEFT JOIN job_postings as jp ON aj.job_posting_id=jp.id 
         LEFT JOIN users as us ON aj.applied_by_id=us.id 
         WHERE aj.created_at BETWEEN CURDATE() AND CURDATE()+1 
         AND aj.current_step>6
         group by Recruiter
         
         union all 
         
         SELECT concat(us.first_name," ",us.last_name) as Recruiter,
                null as submissions,
                COUNT(sc.applied_jobs_id) as selections
         FROM `selected_candidates` as sc 
         LEFT JOIN applied_jobs as aj ON sc.applied_jobs_id=aj.id 
         LEFT JOIN users as us ON aj.applied_by_id=us.id 
         WHERE sc.`created_at` BETWEEN CURDATE() AND CURDATE()+1
         GROUP BY Recruiter
        
    ) as t1
group by t1.Recruiter;

Collectives™ on Stack Overflow

How to Combine columns from multiple query results in mySQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related