Return ONE Row For EmpName

My tables may or may not have the same employee Names listed in them. The problem is I have no way of knowing if Table1 will hold more names or if Table2 will hold more names so I was thinking a union would solve this issue. However as you see in the syntax below it produces to lines for "C" since it exists in both tables. This is my desired output

empName | TotalSW | TotalSNT  
--------+---------+---------
A       |       1 |       0
B       |       1 |       0
C       |       1 |       1
x       |       0 |       1
z       |       0 |       1

With the DDL Below how can a query be written to produce this output?

Create Table #Test1
(empName varchar(100), swas varchar(100))

Create Table #Test2
(empName varchar(100), swont varchar(100))

Insert Into #Test1 (empName, swas) VALUES
('A', 'res1'), ('B', 'tim1'), ('C', 'run34')

Insert Into #Test2 (empName, swont) VALUES
('C', 'er12'), ('z', 'nn12'), ('x', '23rw')

Select empName, TotalSW = COUNT(swas), TotalSNT = ''
FROM #Test1
GROUP BY empName
Union 
Select empName, TotalSW = '', TotalSNT = Count(swont)
FROM #Test2
GROUP BY empName

There are a couple of ways you could do this. You could use a FULL OUTER JOIN which will pull data:

SELECT ISNULL(#Test1.empName, #Test2.empName)
     , TotalSW  = COUNT(#Test1.empName)
     , TotalSNT = COUNT(#Test2.empName)
FROM #Test1 
FULL OUTER JOIN #Test2 ON #Test2.empName = #Test1.EmpName
GROUP BY ISNULL(#Test1.empName, #Test2.empName)

(NOTE: the previous example that used SUM over a CASE statement isn't needed as COUNT does not count NULL values which is what the extra jiggery-pokery was trying to do)

or to keep the UNION method (which may be more or less efficient in more complex examples depending on the rest of the query):

SELECT empName, TotalSW = SUM(TotalSW), SUM(TotalSNT)
FROM   (
        SELECT empName, TotalSW = 1, TotalSNT = 0
        FROM #Test1
        UNION ALL
        SELECT empName, TotalSW = 0, TotalSNT = 1
        FROM #Test2
        ) AS sq
GROUP BY empName

Note the use of UNION ALL not just UNION - otherwise it will filter out duplicates if an employee exists more than once in one table. UNION ALL is also often more efficient because it possibly avoids extra sort operations so unless you need duplicate removal you should use it instead of plain UNION.

Or if you find the CTE syntax more readable than sub-queries (which it often is in more complex examples, though it makes no difference here IMO):

WITH Emps AS (
        SELECT empName, TotalSW = 1, TotalSNT = 0
        FROM #Test1
        UNION ALL
        SELECT empName, TotalSW = 0, TotalSNT = 1
        FROM #Test2
        )
SELECT empName, TotalSW = SUM(TotalSW), SUM(TotalSNT)
FROM   Emps
GROUP BY empName

StackExchange Database Administrators Q#177009, answer score: 7