Several Conditional Count Columns

Several Conditional Count Columns

Post by Josh hee » Sun, 23 Mar 2008 00:09:06

I am fairly new to SQL so please bare with me as I appreciate any help
you all might offer. I have a applicant tracking database and I want
to pull a report with a job id, job title, and several counts of
candidates in various stages of interviews. What I do not know how to
do is to create the several columns of counted data for each job.

Ideal Output:

Job ID Job Title Stage 1 Stage 2 Stage 3 Stage 4
453 DBA 0 3 2 0
5623 .Net 14 2 0 0
134 CTO 0 0 2 2


Current Query:

Select Positions.Position_Id, Positions.Job_Title,
COUNT(Sendout_Records.Sendout_ID) As Interviews_Present
FROM Positions
Left Join Sendout_Records On Sendout_Records.Job_ID = Positions.Job_ID
Where (Positions.Status = 'A') AND (RIGHT(Positions.Position_ID,1) <>
Group By Positions.Position_ID, Positions.Job_Title

The field I want to check against in the count is
Sendout_Records.Status (Active - 0, Active - 1, Reject - 1, etc)

Thanks again for any help.