2 Replies Latest reply: Jun 28, 2017 3:43 AM by Antonio Mancini RSS

    Aggr Distinct Count

    Phil Speight

      I am looking for a way to show a distinct count of vacancies, however some of the vacancies have more than one applicant.  Please see the table below:

       

         

      GroupVacancy RefApplicant RefRecruit.Vacancy_StageRecruit.Vacancy_State
      STAFF_grp1VAC.REF1APP.REF1LonglistingAdvertising
      STAFF_grp1VAC.REF1APP.REF2LonglistingAdvertising
      STAFF_grp2VAC.REF2APP.REF3LonglistingAdvertising
      STAFF_grp1VAC.REF3APP.REF4LonglistingAdvertising
      STAFF_grp1VAC.REF3APP.REF5LonglistingAdvertising
      STAFF_grp1VAC.REF3APP.REF6LonglistingAdvertising
      STAFF_grp1VAC.REF3APP.REF7LonglistingAdvertising
      STAFF_grp1VAC.REF3APP.REF8LonglistingAdvertising
      STAFF_grp1VAC.REF3APP.REF9LonglistingAdvertising
      STAFF_grp1VAC.REF3APP.REF10LonglistingAdvertising
      STAFF_grp1VAC.REF4APP.REF11LonglistingAdvertising
      STAFF_grp1VAC.REF5APP.REF12LonglistingAdvertising
      STAFF_grp1VAC.REF6APP.REF13LonglistingAdvertising
      STAFF_grp3VAC.REF7APP.REF14LonglistingAdvertising
      STAFF_grp1VAC.REF8APP.REF15LonglistingAdvertising
      STAFF_grp1VAC.REF9APP.REF16LonglistingAdvertising
      STAFF_grp1VAC.REF10APP.REF17LonglistingAdvertising
      STAFF_grp2VAC.REF11APP.REF18LonglistingAdvertising
      STAFF_grp1VAC.REF12APP.REF19LonglistingAdvertising
      STAFF_grp1VAC.REF13APP.REF20LonglistingAdvertising
      STAFF_grp1VAC.REF13APP.REF21LonglistingAdvertising
      STAFF_grp1VAC.REF14APP.REF22LonglistingClosed
      STAFF_grp1VAC.REF15APP.REF23LonglistingAdvertising
      STAFF_grp1VAC.REF16APP.REF24LonglistingAdvertising

       

      I want to show that there are 16 Vacancies which are at Longlisting stage.

       

      Expected Results: 16 Vacancies in Longlisting

       

       

       

      Regards

       

       

      Phil