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