SQL - Group By with Left Join

Question

I have two tables. Table A has a list of employee names. Table B is a complex table with information about phone calls made by employees.

My goal is to make a table with columns 'name' and 'callCount'. I am aiming to do this with a 'left join' and a 'group by', but I keep missing the employees that have made no calls. How can I just get it to keep the name and just put a zero there?

Perhaps I am close and someone can point out my typo? Thanks in advance for your help, here is the SQL:

SELECT A.name, COUNT(B.call_id) AS 'outgoing call count' 
FROM EmployeeTable A 
LEFT JOIN CallTable B 
ON A.name = B.call_from_name
WHERE B.call_type LIKE 'outgoing' 
AND B.voice_mail = '0' 
...
GROUP BY A.name 

Solution 1

It's a JOIN not a NULL problem: your filter is changing the OUTER to an INNER JOIN. This means you only get COUNT where you have rows in CallTable (B) rather than the OUTER JOIN you wanted.

SELECT A.name, COUNT(B.call_id) AS 'outgoing call count' 
FROM
   EmployeeTable A 
   LEFT JOIN
   (
   SELECT call_from_name, call_id FROM CallTable
   WHERE call_type LIKE 'outgoing' 
     AND voice_mail = '0'
     AND /* other CallTable filters */
   ) B
   ON A.name = B.call_from_name
WHERE
     /* only EmployeeTable A filters */
GROUP BY A.name 

Edit: after your comment elsewhere, all your filters on B must be in the derived table, not in the outer where.

Solution 2

Because you're using a LEFT JOIN, references to the table defined in the LEFT JOIN can be null. The rows are there, you're just not seeing the count value as zero. Which means you need to convert this NULL value to zero (in this case):

   SELECT A.name, 
          COALESCE(COUNT(B.call_id), 0) AS 'outgoing call count' 
     FROM EmployeeTable A 
LEFT JOIN CallTable B ON B.call_from_name = A.name
                     AND B.call_type LIKE 'outgoing' 
                     AND B.voice_mail = '0' 
    WHERE ...
 GROUP BY A.name 

This example uses COALESCE, an ANSI standard means of handling NULL values. It will return the first non-null value, but if none can be found it will return null. ISNULL is a valid alternative on SQL Server, but it is not portable to other databases while COALESCE is. Here's an MSDN article comparing the two functions.

Comments

[ZygD]

COALESCE aint needed: it's the WHERE clause filter on B making it an INNER JOIN

[Brandi]

I have tried ISNULL and COALESCE, both give the same result. I may be doing something else wrong? I have more where clauses than I listed, but they are all necessary for determining the count.

[Tim Rupe]

Be careful about using LIKE when you don't need the wildcard comparisons. It can really kill your query speed. Just do a regular equality comparison: WHERE call_type = 'outgoing'

[dburges]

For further explanation: wiki.lessthandot.com/index.php/WHERE_conditions_on_a_LEFT_JO‌​IN

[Hetvi]

Thank you so much,I was struggled since last 4 hours.Finally its solved.Thanks again

[Saneesh kunjunni]

Thank you for your answer. I got the result within seconds and nicely worked with this sample