SQL & PL/SQL

Hi All,

By using this query we are getting the below output. Here "CNT" is count of Males/Females in  a business unit but we need to display another column i.e Average of Male/Female in a business group.

For ex: Average value-(214/(214+131))*100=62%

Please share inputs to achieve"Average"  this requirement in a single SQL query.

  Current Output

-------------

Expected Output

--------------

Current Query

--------------

SELECT per_lg.SEX

,hrouf_bu.NAME Business Unit

,count(*) cnt

FROM per_people_legislative_f per_lg

,HR_ORGANIZATION_UNITS_F_TL hrouf_bu

,HR_ORG_UNIT_CLASSIFICATIONS_F hrouc_bu

,PER_ALL_ASSIGNMENTS_M PAAM

,per_persons pp

,per_periods_of_service ppos

WHERE 1=1

AND PAAM.PERSON_ID = per_lg.PERSON_ID

and hrouf_bu.ORGANIZATION_ID   = paam.BUSINESS_UNIT_id

AND hrouf_bu.ORGANIZATION_ID     = hrouc_bu.ORGANIZATION_ID

AND hrouc_bu.CLASSIFICATION_CODE = 'FUN_BUSINESS_UNIT'

AND hrouf_bu.LANGUAGE = 'US' 

and paam.assignment_type in('E')

AND paam.effective_latest_change  = 'Y'

AND PAAM.PRIMARY_FLAG='Y'

AND pp.PERSON_ID = per_lg.PERSON_ID

and trunc(sysdate) between PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE

AND trunc(sysdate) BETWEEN hrouf_bu.EFFECTIVE_START_DATE AND hrouf_bu.EFFECTIVE_END_DATE

AND trunc(sysdate) BETWEEN hrouc_bu.EFFECTIVE_START_DATE AND hrouc_bu.EFFECTIVE_END_DATE

And trunc(sysdate) BETWEEN per_lg.effective_start_date AND per_lg.effective_end_date

and ppos.person_id = paam.person_id

and ppos.period_of_service_id = paam.period_of_service_id

and ppos.date_start =(select max(ppos1.date_start)

                       from  per_periods_of_service ppos1

    where ppos1.person_id=paam.person_id

    and  ppos1.date_start<=trunc(sysdate))

group by hrouf_bu.NAME, per_lg.SEX

order by 2

Thanks,

Visu