Combine results from 2 SQL queries

user_10691536 0
Viewed 44

I've got two tables with similar data that I'd like to compare. The following queries provide a count of employees in each table:

SELECT Extract_Month, COUNT(Employee_Number) as Staff
FROM Staff
GROUP BY Extract_Month;

SELECT Extract_Month, COUNT(Employee_Number) as MX
FROM Staff_mx
GROUP BY Extract_Month;

Which results in the following

2019-04-01  17246
2019-05-01  17194
2019-06-01  17252

2019-04-01  17140
2019-05-01  17194
2019-06-01  17434

I want a query to combine the results in the following format:

            Staff MX
2019-04-01  17246 17140
2019-05-01  17194 17194
2019-06-01  17252 17434

I've tried this, which contained the results I wanted, but didn't present them correctly:

SELECT s.Extract_Month, COUNT(s.Employee_Number) as Staff, COUNT(mx.Employee_Number) as MX
FROM Staff s
    LEFT JOIN
        staff_mx mx
        ON s.Employee_Number = mx.Employee_Number 
        AND s.Extract_Month = mx.Extract_Month 
GROUP BY s.Extract_Month
UNION ALL
SELECT mx.Extract_Month, COUNT(s.Employee_Number) as Staff, COUNT(mx.Employee_Number) as MX
FROM Staff s
    RIGHT JOIN
        staff_mx mx
        ON s.Employee_Number = mx.Employee_Number 
        AND s.Extract_Month = mx.Extract_Month 
GROUP BY mx.Extract_Month

which gave me the following, but not the desired result:

            Staff   MX    
2019-04-01  17246   16830
2019-05-01  17194   16820
2019-06-01  17252   16784
2019-04-01  16830   17140
2019-05-01  16820   17194
2019-06-01  16784   17434

Any help would be appreciated!

union join sql-server sql
3 Answers

You can use JOIN:

SELECT s.Extract_Month, s.staff, sm.mx
FROM (SELECT Extract_Month, COUNT(Employee_Number) as Staff
      FROM Staff
      GROUP BY Extract_Month
     ) s JOIN
     (SELECT Extract_Month, COUNT(Employee_Number) as MX
      FROM Staff_mx
      GROUP BY Extract_Month
     ) sm
     ON sm.Extract_Month = s.Extract_Month;

If the two tables don't necessarily have the same months, then use FULL JOIN and COALESCE():

SELECT COALESCE(s.Extract_Month, sm.Extract_Month) as Extract_Month,
       COALESCE(s.staff, 0) as staff,
       COALESCE(sm.mx, 0) as mx
FROM (SELECT Extract_Month, COUNT(Employee_Number) as Staff
      FROM Staff
      GROUP BY Extract_Month
     ) s FULL JOIN
     (SELECT Extract_Month, COUNT(Employee_Number) as MX
      FROM Staff_mx
      GROUP BY Extract_Month
     ) sm
     ON sm.Extract_Month = s.Extract_Month;
user_11440350

Do conditional aggregation :

with cte as (
     SELECT Extract_Month, Employee_Number as Staff, 'staff' AS flag
     FROM Staff
     UNION ALL
     SELECT Extract_Month, Employee_Number as Staff, 'mx' AS flag
     FROM Staff_mx
)
SELECT Extract_Month, 
       COUNT(CASE WHEN flag = 'Staff' THEN Employee_Number END) AS Staff,
       COUNT(CASE WHEN flag = 'mx' THEN Employee_Number END) AS mx
FROM cte c
GROUP BY Extract_Month;
user_55294450

Perhaps something like this is what you're after:

SELECT ISNULL(S.Extract_Month,Sm.Extract_Month) AS Extract_Month,
       S.Staff,
       Sm.MX
FROM (SELECT Extract_Month, COUNT(Employee_Number) as Staff
      FROM Staff
      GROUP BY Extract_Month) S
     FULL OUTER JOIN (SELECT Extract_Month, COUNT(Employee_Number) as MX
                      FROM Staff_mx
                      GROUP BY Extract_Month) Sm ON S.Extract_Month = Sm.Extract_Month;

I use a FULL OUTER JOIN here just in case one table doesn't have any rows for a given Extract Month.

user_20299830
Related