with T1 as
(select distinct pays,avg(effectif_menage) as indic1 from total.v_liste_menage where effectif_menage>0 group by pays)
,T1_tot as
(select avg(effectif_menage) as indic1_tot from total.v_liste_menage where effectif_menage>0)
,T2 as
( select distinct pays ,strate, count(no_menage)*txa as nb_menage from total.liste_site inner join total.calcul_txa using (strate,pays)
inner join total.v_liste_menage using (no_site,pays) where
(nb_homme_peche+nb_femme_peche+nb_homme_transfo+nb_femme_transfo+nb_homme_commer+nb_femme_commer)>0 or (pratique_elevage is true or pratique_agri is true)
or (mecanicien is true or charpentier is true or commercant_met is true) group by strate,pays,txa )
,T2_P as
(select distinct pays,sum(nb_menage) as indic2 from T2
group by pays order by pays)
,pop_total as
(select distinct pays, population as poptot from total.infos_pays)
,T2_tot as
((select distinct '' as tri,pays,indic1*indic2 as indic1,poptot/1000000.0 as indic2,(indic1*indic2/poptot)*100 as indic3 from T1 inner join T2_P using(pays) inner join pop_total using(pays) )
union
(select distinct 'Ensemble UEMOA','',indic1_tot*nb_tot as indic1,poptot/1000000.0 as indic2,100*indic1_tot*nb_tot/poptot as indic3
from (select sum(indic2) as nb_tot from T2_P) T2_P_P,T1_tot,(select sum(poptot) as poptot from pop_total) R1))
select * from T2_tot order by tri,pays