5

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
4

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;
7

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
2

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. – Erik Darling Jun 22 '17 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. – David Spillett Jun 22 '17 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 '17 at 14:55
  • @irimias but the count is wrong. – Erik Darling Jun 22 '17 at 15:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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