hi all,
i have a query i need to find out average age b/w Associate team member to Senior Team member.
select floor(avg((SYSDATE - date_of_birth)/365)) age1,
pj.name
from per_all_people_f papf,
per_business_groups pbg,
per_all_assignments_f paaf
,hr.per_person_types ppt
,hr.per_person_type_usages_f pptu
,hr_locations_all hla
,per_jobs pj
Where
papf.business_group_id = pbg.ORGANIZATION_ID
AND papf.person_id=paaf.person_id
AND papf.person_id = pptu.person_id(+)
AND ppt.person_type_id(+) = pptu.person_type_id
AND hla.location_id=paaf.location_id
AND pj.job_id = paaf.job_id
AND ppt.user_person_type not in ('Contact','Family','Emergency Contact','Ex-applicant','Ex-contingent Worker','Ex-employee','Ex-employee and Applicant','Applicant','Applicant and Ex-applicant','Beneficiary','Agency Employee')
and papf.business_group_id=81
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date and papf.effective_end_date
AND TRUNC(sysdate) between paaf.effective_start_date and paaf.effective_end_date
AND pj.name in ('Associate Team Member','Senior Team Member','Associate Engineer')
group by pj.name;
by using above query i am getting output like below
age designation
29 Associate Engineer
29 Senior Team Member
24 Associate Team Member
now i need to find out average age of these 3 designations.