Post reply

Count if condition

  • September 27, 2006 at 3:05 pm

    #117011

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

  • September 27, 2006 at 4:01 pm

    #662876

    Mita,

    I hope this is what u wanted. Please let me know if this is not right.

    Select  Providerdate,Region,DHB,DHBName,PHO,PHOName,Practice,Practicename,

     PracticeAddress,Practice_Startdate,Practice_Enddate,Practice_Test.Practitioner,

     PractitionerName,Practice_Test.Practitioner_Startdate,Practitioner_Enddate

     ,YCount,NCount

    from Practice_Test INNER JOIN

     (

      Select  Practitioner,  sum(Case when Upper(Locum) ='YES' Then 1 Else 0 End) as YCount,

         sum(Case when Upper(Locum) ='NO' Then 1 Else 0 End) as NCount

      from Practice_Test

      group by Practitioner

     &nbsp PractitionerGroup ON Practice_Test.Practitioner = PractitionerGroup.Practitioner

    Thanks

    Sreejith

  • September 27, 2006 at 5:35 pm

    #662881

    hi Sreejith

    thanks for the help

    i tried this query but there is some syntax error which says- incorrect synax near group.

    "PractitionerGroup ON Practice_Test.Practitioner = PractitionerGroup.Practitioner"

    I could not understand this above statement.

    What do you mean by practitionergroup??

  • September 27, 2006 at 6:54 pm

    #662887

    "Select  Practitioner,  sum(Case when Upper(Locum) ='YES' Then 1 Else 0 End) as YCount,

         sum(Case when Upper(Locum) ='NO' Then 1 Else 0 End) as NCount

      from Practice_Test

      group by Practitioner"

    when i try this bit, it works but the part above the inner join,something wrong with it.

     

  • September 28, 2006 at 2:44 am

    #662928

    I believe the closing brace after group by Practitioner is missing, PractitionerGroup is the derived table and the syntax should work without any errors.

     

    Prasad Bhogadi
    www.inforaise.com

  • September 28, 2006 at 10:41 am

    #663079

    Agree re closing paren. Looks like something replaced it with a smiley face emoticon! Pretty dumb "feature" in a technically oriented forum

  • September 28, 2006 at 2:24 pm

    #663152

    I didn't realise that ) was repalced with smily face. These should be plain text box without emiticons.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply