with T1 as
(select  distinct pays,sexe,sum((nb_scolarise_primaire+nb_scolarise_autres)*txb*txc*txa) as scolarise,
sum(nombre_total*txb*txc*txa) as total,count(distinct no_menage) as nbrep
from total.liste_site B
inner join total.calcul_txa using(pays,strate)
inner join total.calcul_txc using(pays,strate)
inner join total.v_liste_menage C
using (pays,no_site) inner join total.calcul_txb using(pays,no_site) inner join total.v_menage_enfant_0_12 D using(pays,no_menage)
where classe_age like '6-12'
and (nb_scolarise_primaire+nb_scolarise_autres)<=nombre_total and nombre_total>0
group by pays,sexe
)
,final as
(select distinct ' ' as ens,pays,(G.scolarise/G.total)*100 as garcons,(F.scolarise/F.total)*100 as filles,
((F.scolarise/F.total)*100)/((G.scolarise/G.total)*100) as ratio
from
(select * from T1 where sexe like 'F%') F inner join (select * from T1 where sexe like 'G%') G using (pays)
)
,ensemble as
(
with ens1 as (select distinct sexe,100*sum(scolarise)/sum(total) as TOTAL from T1 group by sexe)
select 'Ensemble UEMOA'::text as ens,' '::text as pays,garcons,filles,filles/garcons as ratio from (select TOTAL as filles from ens1 where sexe like 'F%') F,(select TOTAL as garcons from ens1 where sexe like 'G%') G
)
select * from final
union
select * from ensemble