with total as
(select distinct pays,strate,count( no_menage) *txa as nb_tot,txa from
total.liste_site  B
inner join total.calcul_txa using (pays,strate) inner join total.v_liste_menage using(pays,no_site)
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 pays,txa,strate)
,exp_coll as
(select distinct pays,strate,count(distinct no_menage) *txa as TOTAL from
total.liste_site  B inner join total.calcul_txa using (pays,strate) inner join total.v_liste_menage using(pays,no_site)
where mecanisme_prevention_conflits=-1 group by pays,txa,strate)
,final as
(
select distinct pays,sum(TOTAL) as nb_tot_mecanisme,sum(nb_tot) as nb_menage_tot,sum(TOTAL)/sum(nb_tot)*100 as TOTAL
from total inner join exp_coll using(pays,strate)
group by pays  order by pays)
select ' '::text as ens,pays,TOTAL from final
union
select 'Ensemble UEMOA'::text as ens,''::text as pays,sum(nb_tot_mecanisme)/sum(nb_menage_tot)*100 from final