How to count the number of records in a group by the criterion and if there are none, then display zero?

Question

SQL Server

There is a database in which you need to calculate the total number of students who fell ill with the mentioned diagnosis during the year for each faculty: group - the number of sick people, if there are none, then the number should be equal to zero.

Database Schema

Answer 1

COUNT and returns 0 if there are no rows that match the condition.
If all faculties are needed, then:

SELECT FacultyName, ISNULL(CN.Co,0)
  FROM Faculty
    LEFT JOIN (SELECT [Card].FacultyId,  COUNT(*) AS Co
FROM [Card] 
JOIN Diagnosis  ON [Card].CardId = Diagnosis.CardId
JOIN Diagnosies  ON Diagnosies.Id = Diagnosis.Diagnosis
WHERE Diagnosies.DiagnosisName = N'грип' AND YEAR(Diagnosis.VisitDate) = 2000
GROUP BY [Card].FacultyId) AS CN
      ON CN.FacultyId = Faculty.FacultyID