With general as
(
select distinct pays,no_site,no_menage,date_enquete_m,strate,electricite_maison,
latrines_fam,statut_occupant,habitat_securise,sol_maison,materiau_maison,source
from total.liste_site
 inner join total.v_liste_menage using(pays,no_site)
 left join total.v_menage_electricite_source using(pays,no_menage)
 order by no_menage
)
,presence_electricite as
(
select distinct pays,strate,sum(nb_menage)*txa*txc as nb_indic1 from
(select distinct no_site,pays,strate,electricite_maison,source,count(distinct no_menage)*txb as nb_menage
from general inner join total.calcul_txb using(pays,no_site) where date_enquete_m is not null and source not like 'Pile%'
group by pays,txb,no_site,electricite_maison,strate,source) tmp1
inner join total.calcul_txa B using(pays,strate)
inner join total.calcul_txc using(pays,strate)
 where electricite_maison=-1  group by pays,txa,strate,txc
)
,presence_latrines as
(
select distinct pays,strate,sum(nb_menage)*txa*txc as nb_indic2 from
(select distinct no_site,pays,strate,latrines_fam,count(distinct no_menage)*txb as nb_menage from general inner join total.calcul_txb using(pays,no_site)
where date_enquete_m is not null group by pays,txb,no_site,latrines_fam,strate) tmp1
inner join total.calcul_txa B using(pays,strate)
inner join total.calcul_txc using(pays,strate)
 where latrines_fam=-1  group by pays,txa,txc,strate
)
,puit_dans_cour as
(
select distinct pays,strate,sum(nb_menage)*txa*txc as nb_indic3 from
(select distinct no_site,pays,strate,eau.lieu as lieu_eau,count(distinct no_menage)*txb as nb_menage
from general inner join total.v_menage_eau eau using(pays,no_menage) inner join total.calcul_txb using(pays,no_site) where date_enquete_m is not null
group by pays,txb,no_site,eau.lieu,strate) tmp1
inner join total.calcul_txa B using(pays,strate)
inner join total.calcul_txc using(pays,strate)
where lieu_eau in ('puits dans la cour','robinet dans la maison ou la cour')
group by pays,txa,txc,strate
)
,sol_dur as
(
select distinct pays,strate,sum(nb_menage)*txa*txc as nb_indic4 from
(select distinct no_site,pays,strate,sol_maison,count(distinct no_menage)*txb as nb_menage from general inner join total.calcul_txb using(pays,no_site)
where date_enquete_m is not null group by pays,txb,no_site,sol_maison,strate) tmp1
inner join total.calcul_txa B using(pays,strate)
inner join total.calcul_txc using(pays,strate)
where sol_maison=-1   group by pays,txa,txc,strate
)
,mat_maison_paille as
(
select distinct pays,strate,sum(nb_menage)*txa*txc as nb_indic5 from
(select distinct no_site,pays,strate,count(distinct no_menage)*txb as nb_menage
from general inner join total.calcul_txb using(pays,no_site)
where no_menage not in (select distinct no_menage from general where materiau_maison like  'Paille')  and
date_enquete_m is not null
group by pays,txb,no_site,strate) tmp1
inner join total.calcul_txa B using(pays,strate)
inner join total.calcul_txc using(pays,strate)
group by pays,txa,txc,strate
)
,nb_menage_region as
(
select pays,strate,sum(nb_menage_tot)*txa*txc as nb_menage_tot from

(SELECT DISTINCT pays,strate,no_site, count(distinct no_menage)*txb AS nb_menage_tot
           from general
inner join total.calcul_txb using(pays,no_site)
where date_enquete_m is not null
      GROUP BY pays,txb,strate,no_site) T1
inner join total.calcul_txa B using(pays,strate)
inner join total.calcul_txc using(pays,strate)
      group by pays,strate,txa,txc

 )
,
final as
(select pays,
100*sum(nb_indic1)/sum(nb_menage_tot) as indic1,
100*sum(nb_indic2)/sum(nb_menage_tot) as indic2,
100*sum(nb_indic3)/sum(nb_menage_tot) as indic3,
100*sum(nb_indic4)/sum(nb_menage_tot) as indic4,
(100*sum(nb_indic5)/sum(nb_menage_tot)) as indic5
  from
nb_menage_region  left join presence_electricite using(pays,strate)
left join presence_latrines using(pays,strate)
left join puit_dans_cour using(pays,strate)

left join sol_dur using(pays,strate)
left join mat_maison_paille using(pays,strate)
group by pays),

ensemble_uemoa as
(select 'Ens'::text as ens,''::text as pays,
100*sum(nb_indic1)/sum(nb_menage_tot) as indic1,
100*sum(nb_indic2)/sum(nb_menage_tot) as indic2,
100*sum(nb_indic3)/sum(nb_menage_tot) as indic3,
100*sum(nb_indic4)/sum(nb_menage_tot) as indic4,
(100*sum(nb_indic5)/sum(nb_menage_tot)) as indic5
  from
nb_menage_region  left join presence_electricite using(pays,strate)
left join presence_latrines using(pays,strate)
left join puit_dans_cour using(pays,strate)

left join sol_dur using(pays,strate)
left join mat_maison_paille using(pays,strate)
)
,final2 as
(
select ''::text as ens,*,(indic1+indic2+indic3+indic4+indic5)/5  as indic6 from final
union
select *,(indic1+indic2+indic3+indic4+indic5)/5  as indic6 from ensemble_uemoa
)
select * from final2