I have an SQL query that I want to return a bunch of counts. However, I need to go through all records that fit my criteria and then return the counts of certain BIT fields where they are equal to 1 (or true).
My SQL so far is as follows. Unfortunately it is returning the count of ALL Body, Contact, and Observation values whether they are true or false.
When I know the database the Employee details is coming from I will also have to join to that usng the EmployeeId. I don't know if this is going to be a problem or not.
So, what I want is the count of Body, Contact, and Observation for each employee where the ProjectId = the passed value.
SELECT EmployeeId AS EmployeeNumber, 'Employee Name' AS EmployeeName, COUNT(AuditInterviewId) AS AuditCount, COUNT(Body) AS BodyCount,
COUNT(Contact) AS ContactCount, COUNT(Observation) AS ObservationCount
WHERE (ProjectId = @ProjectId)
GROUP BY EmployeeId