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
Select empName, TotalSW = '', TotalSNT = Count(swont)
FROM #Test2
GROUP BY empName

3 Answers 3


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
    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.
    – irimias
    Jun 22, 2017 at 14:55
  • @irimias but the count is wrong. Jun 22, 2017 at 15:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.