Get count of 'true' values in records.

dij8
dij8 used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
For your bit columns, change the COUNT() to:

SUM(CONVERT(int, YourBitColumn))
Top Expert 2012

Commented:
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
Top Expert 2012

Commented:
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
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Top Expert 2010

Commented:
acperkins,

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

Regards,

Patrick

Author

Commented:
So easy when you know how. :)
Top Expert 2012

Commented:
Probably the CONVERT is better.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial