CODEWITHSUNDEEP
sql
Brad
Brad

Reputation: 21160

counting matches in sql

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

Upvotes: 0

Views: 11117

Answers (5)

Philip Fourie
Philip Fourie

Reputation: 116827

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.*)

Upvotes: 1

Jim G.
Jim G.

Reputation: 15365

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

Upvotes: 1

RBarryYoung
RBarryYoung

Reputation: 56725

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

Upvotes: 4

araqnid
araqnid

Reputation: 133402

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.

Upvotes: 5

pingw33n
pingw33n

Reputation: 12510

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

Upvotes: 4

Related Questions