asked on

Get count of 'true' values in records.

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
FROM	AuditInterview
WHERE	(ProjectId = @ProjectId)
GROUP BY EmployeeId

Open in new window

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

Try this:

SELECT EmployeeId EmployeeNumber,
[Employee Name] EmployeeName,
SUM(AuditInterviewId) AuditCount,
SUM(Body) BodyCount,
SUM(Contact) ContactCount,
SUM(Observation) ObservationCount
FROM AuditInterview
WHERE ProjectId = @ProjectId
GROUP BY
EmployeeId

Anthony Perkins

Or:
SELECT EmployeeId EmployeeNumber,
[Employee Name] EmployeeName,
SUM(
                  CASE AuditInterviewId
                        WHEN 1 THEN 1
                        ELSE 0
            END) AuditCount,
SUM(
                  CASE Body
                        WHEN 1 THEN 1
                        ELSE 0
            END) BodyCount,
SUM(
                  CASE Contact
                        WHEN 1 THEN 1
                        ELSE 0
            END) ContactCount,
SUM(
                  CASE Observation
                        WHEN 1 THEN 1
                        ELSE 0
            END) ObservationCount
FROM AuditInterview
WHERE ProjectId = @ProjectId
GROUP BY
            EmployeeId

Patrick Matthews

acperkins,

I'm kind of curious now as to whether the SUM(CONVERT(...)) approach or the SUM(CASE...) approach is
faster :)

Regards,

Patrick

dij8

ASKER

So easy when you know how. :)

Anthony Perkins

Probably the CONVERT is better.