counting matches in sql

Question

I would like to track how many surveys have been done by different departments at work. This is fairly easy to do by using "Group by" and counting the number of matches.

SELECT     Departments.DepartmentName, count(*) as [survey count]
FROM         Departments INNER JOIN
                  Surveys ON Departments.DepartmentID =  Surveys.DepartmentID
 GROUP BY Departments.DepartmentName

but this only shows departments that have completed surveys. How would I have the departments that have not completed surveys represented on the results list as a zero count?

Update:

SELECT     Departments.DepartmentName AS Department, 
        COUNT( Surveys.DepartmentID) AS      [survey count]
        , Departments.DepartmentID
        FROM          Surveys FULL OUTER JOIN
                  Departments ON  Surveys.DepartmentID = Departments.DepartmentID
   GROUP BY Departments.DepartmentName, Departments.DepartmentID

Answer 1

Replace INNER JOIN with LEFT JOIN. It will select all departments even no associated survey exists.

Answer 2

SELECT Departments.DepartmentName, count(Surveys.DepartmentID) as [survey count]
FROM Departments
LEFT OUTER JOIN Surveys
ON Departments.DepartmentID =  Surveys.DepartmentID
GROUP BY Departments.DepartmentName

Answer 3

SELECT     Departments.DepartmentName, count(Surveys.*) as [survey count]
FROM         Departments LEFT JOIN
                  Surveys ON Departments.DepartmentID =  Surveys.DepartmentID
 GROUP BY Departments.DepartmentName

Note the change on the LEFT JOIN and count(Surveys.*)

Answer 4

You need a "left outer join" instead of an "inner join" - that will produce rows in the output with all the columns corresponding to "surveys" as null where there was no match for the join condition.

You'll need to update your query to "count(Surveys.DepartmentID)" or "sum(case when surveys.departmentID is not null then 1 else 0 end)" to avoid counting such rows as a single submission.

Answer 5

Just change your INNER JOIN to a LEFT JOIN and change the COUNT() to only reference the SURVEYS. That'll probably work as is then.

SELECT     Departments.DepartmentName,
           count(Surveys.DepartmentID) as [survey count]
FROM         Departments LEFT JOIN
              Surveys ON Departments.DepartmentID =  Surveys.DepartmentID
 GROUP BY Departments.DepartmentName