with nb_menage_tot as
(
select distinct pays,sum(nb_site*txa) as nb_menage from
(select pays,strate,count(distinct no_menage) as nb_site
from total.liste_site inner join total.v_liste_menage using(no_site,pays)
group by pays,strate) tmp
inner join total.calcul_txa using(strate,pays)
group by pays

)
,commodites_site as
(
select distinct PAYS,commodites,sum(nb_comm)/nb_menage*100 as nb_comm from
(
select distinct PAYS,strate,commodites,
count(distinct no_menage)*txa as nb_comm from total.liste_site inner join total.calcul_txa
using (strate,pays)
 inner join total.v_commodites using(no_site,pays)
  inner join total.v_liste_menage using(no_site,pays)
where proximite in ('Sur place') group by pays,commodites,txa,strate) tmp inner join
nb_menage_tot using(pays)
group by PAYS,commodites,nb_menage
)

,commodites_site_uemoa as
(
select distinct commodites,sum(nb_comm)/nb_menage*100 as nb_comm from
(
select distinct PAYS,strate,commodites,
count(distinct no_menage)*txa as nb_comm from total.liste_site inner join total.calcul_txa
using (strate,pays)
 inner join total.v_commodites using(no_site,pays)
  inner join total.v_liste_menage using(no_site,pays)
where proximite in ('Sur place') group by pays,commodites,txa,strate) tmp ,(select sum(nb_menage) as nb_menage from nb_menage_tot) R1
group by commodites,nb_menage
),
par_pays as
(
select *
from
(select PAYS,nb_comm as Indic1 from commodites_site where commodites like 'Ecole primaire' ) T1
left join
(select PAYS,nb_comm as Indic2 from  commodites_site where commodites like 'Puit amélioré ou forage, adduction%') T2 using (PAYS)
left join
(select PAYS,nb_comm as Indic3 from commodites_site where commodites like 'Réseau télé%') T3 using (PAYS)
left join
(select PAYS,nb_comm as Indic4 from commodites_site where commodites like 'Réseau publique%') T4 using (PAYS)
left join
(select PAYS,nb_comm as Indic5 from commodites_site where commodites like 'Centre %') T5 using (PAYS)
left join
(select PAYS,nb_comm as Indic6 from commodites_site where commodites like 'Marché%') T6 using (PAYS)
)
, ensemble as
(
select *
from
(select  ''::text as PAYS,nb_comm as Indic1 from commodites_site_uemoa where commodites like 'Ecole primaire' ) T1
,
(select nb_comm as Indic2 from  commodites_site_uemoa where commodites like 'Puit amélioré ou forage, adduction%') T2
,
(select nb_comm as Indic3 from commodites_site_uemoa where commodites like 'Réseau télé%') T3 ,
(select nb_comm as Indic4 from commodites_site_uemoa where commodites like 'Réseau publique%') T4
,
(select nb_comm as Indic5 from commodites_site_uemoa where commodites like 'Centre %') T5
,
(select nb_comm as Indic6 from commodites_site_uemoa where commodites like 'Marché%') T6
)
,final as
(select '' as ens,*,(Indic1+Indic2+Indic3+Indic4+Indic5+Indic6)/6 as indic7 from par_pays
union
select 'Ensemble UEMOA' as ens,*,(Indic1+Indic2+Indic3+Indic4+Indic5+Indic6)/6 as indic7 from ensemble
)
select * from final order by ens,pays