Count if condition

hi guys

I need to do a count on a column in my table but i have to check for a condition first.

Here is my sample data

ProviderdateRegionDHBDHBNamePHOPHONamePracticePracticenamePracticeAddressPractice_StartdatePractice_EnddatePractitionerPractitionerNamePractitioner_StartdatePractitioner_EnddateLocum1/01/2006Central091Capital & Coast DHB585171Kapiti PHO-585171585171_0000045Horatepai15 Epiha Street, Paraparaumu1/07/200329201Geraldine Victoria MacKenzie Jordan8/06/200531/10/2005no1/01/2006Central091Capital & Coast DHB585171Kapiti PHO-585171585171_0000045Horatepai15 Epiha Street, Paraparaumu1/07/200322079Glenn Morton Colquhoun1/03/2006NO1/01/2006Central091Capital & Coast DHB585171Kapiti PHO-585171585171_0000045Horatepai15 Epiha Street, Paraparaumu1/07/200314121Meaburn Charles Staniland1/07/2003NO1/01/2006Central091Capital & Coast DHB585171Kapiti PHO-585171585171_0000045Horatepai15 Epiha Street, Paraparaumu1/07/20039877Philip White1/07/2003NO1/01/2006Central091Capital & Coast DHB585171Kapiti PHO-585171585171_0000045Horatepai15 Epiha Street, Paraparaumu1/07/200311678Susan Prudence Wilson1/07/2003NO1/01/2006Central091Capital & Coast DHB585171Kapiti PHO-585171585171_0000045Horatepai15 Epiha Street, Paraparaumu1/07/20033881Wilfred Travis Wilson5/01/2002yes1/01/2006Central091Capital & Coast DHB585171Kapiti PHO-585171585171_0000052Paraparaumu Medical Centre92-94 Kapiti Road, Paraparaumu1/07/200322742Adrian Howard Beaumont Gilliland13/01/200528/10/2005NO1/01/2006Central091Capital & Coast DHB585171Kapiti PHO-585171585171_0000052Paraparaumu Medical Centre92-94 Kapiti Road, Paraparaumu1/07/200318188Amanda Mary Clarke1/07/2003NO1/01/2006Central091Capital & Coast DHB585171Kapiti PHO-585171585171_0000052Paraparaumu Medical Centre92-94 Kapiti Road, Paraparaumu1/07/200332909Andre Honda Garib29/12/2005no1/01/2006Central091Capital & Coast DHB585171Kapiti PHO-585171585171_0000052Paraparaumu Medical Centre92-94 Kapiti Road, Paraparaumu1/07/200313042Christopher Alan Fawcett1/07/2003NO1/01/2006Central091Capital & Coast DHB585171Kapiti PHO-585171585171_0000052Paraparaumu Medical Centre92-94 Kapiti Road, Paraparaumu1/07/200316556Jennifer Maree O'Donnell1/07/200322/12/2005YES1/01/2006Central091Capital & Coast DHB585171Kapiti PHO-585171585171_0000052Paraparaumu Medical Centre92-94 Kapiti Road, Paraparaumu1/07/200313650Timothy Stapleton Smith1/07/2003NO1/01/2006Central091Capital & Coast DHB585171Kapiti PHO-585171585171_0000046Paul Norton Surgery51 Kapiti Road, Paraparaumu1/07/200314621Paul Stephen Norton1/07/2003NO1/01/2006Central091Capital & Coast DHB585171Kapiti PHO-585171585171_0000053Raumati Road Surgery23 Raumati Road, Raumati Beach, Paraparaumu1/07/200311202Katherine Dana Stone3/10/20057/10/2005yes1/01/2006Central091Capital & Coast DHB585171Kapiti PHO-585171585171_0000053Raumati Road Surgery23 Raumati Road, Raumati Beach, Paraparaumu1/07/200313048Robyn Lynette Crow1/07/2003NO

now i have to add two new columns as LocumYes and LocumNo
In Locumyes column I have to count the number of 'yes' in the locum column grouped by practitioner and in locumno i have to count the number of 'no' for that practitioner.

The situation is a practitioner can work on more than one practice and he can work as a 'yes' locum in one,'no' locum' in another or vice versa.It can be of any sort of combination.

so I have to do a query which should group by practitioner and count the number of yes and no for each practitioner..

Thanks

Mita

Hi. You can do this with subqueries included in the SELECT statement.

Let's suppose your table is named tblPractices:

SELECT Practicioner,

(SELECT COUNT(Locum) FROM tblPractices LocumYes WHERE Locum = 'yes' AND LocumYes.Practicioner = tblPractices.Practicioner) AS NumYes,

(SELECT COUNT(Locum) FROM tblPractices LocumNo WHERE Locum = 'no' AND LocumNo.Practicioner = tblPractices.Practicioner) AS NumNo

FROM tblPractices

GROUP BY Practicioner

Tony Alicea
http://www.theabstractionpoint.com
clarity of mind and creativity in application software development...