# 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
``````

The quick and dirty method is to just wrap your query in another aggregation.

``````SELECT   x.empName, SUM(x.TotalSW), SUM(x.TotalSNT)
FROM     (
SELECT   empName, COUNT(swas) AS TotalSW, 0 AS TotalSNT
FROM     #Test1
GROUP BY empName
UNION ALL
SELECT   empName, 0 AS TotalSW, COUNT(swont) AS TotalSNT
FROM     #Test2
GROUP BY empName ) AS x
GROUP BY x.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
``````

You can try something like:

``````select empName, count(swas), count(swont)
from (
select empName, swas, null as swont
from #Test1
union
select empName, null as swas, swont
from #Test2
) t
group by empName
``````
• You need to use `SUM` here, `COUNT` double-counts the values for employee C when you aggregate. Jun 22, 2017 at 14:46
• Also you want `UNION ALL` if there might be duplicates to count in each table. Plain `UNION` implies `DISTINCT` so would merge such duplicates. `UNION ALL` is often more efficient too as it avoids potentially needing extra sort operations needed to find the duplicates. Jun 22, 2017 at 14:54
• @sp_BlitzErik: count is ok since it counts not null values, so for employee C we only have one line, as asked in the question. Jun 22, 2017 at 14:55
• @irimias but the count is wrong. Jun 22, 2017 at 15:21