with autocons1 as
(

select distinct pays,strate,no_site,count(distinct no_menage)*txb as nb_menage from total.liste_site
 inner join total.v_liste_menage using(pays,no_site)
 inner join total.calcul_txb using(pays,no_site)
where (part_capture_vendue+part_capture_remis_transformation+part_capture_remis_vente)=0
and (part_capture_autoconsomm+part_capture_dons)>0
group by txb,no_site,strate,pays
)
,autocons2 as
(
select distinct pays,sum(nb_menage)*txa*txc as nb_auto from
autocons1 A inner join total.calcul_txa B using(pays,strate)
inner join total.calcul_txc using(pays,strate)
group by pays,txa,txc
)
,nb_menage_region as
(
SELECT DISTINCT pays, count(DISTINCT v_liste_menage.no_menage)*txa*txc AS nb_menage_tot
           FROM total.liste_site
           inner join total.calcul_txa using (pays,strate)
           inner join total.calcul_txc using (pays,strate)
      JOIN total.v_liste_menage USING (pays,no_site)
      --Cette phrase peut être ajouté si on veut faire le raport uniquement sur ceux qui ont répondu  au moins a une des questions
      --where (part_capture_vendue+part_capture_remis_transformation+part_capture_remis_vente+part_capture_autoconsomm+part_capture_dons)>0
     GROUP BY pays,txa,txc
 )
 ,tot as
((select distinct '' as ens,pays,(sum(nb_auto)/sum(nb_menage_tot)*100) as indic1 from autocons2
 right join nb_menage_region using(pays)
 group by pays order by pays)
union
(select distinct 'Ensemble UEMOA' as ens,'',(sum(nb_auto)/sum(nb_menage_tot)*100) as indic1 from autocons2
 right join nb_menage_region using(pays)))
 select * from tot order by ens,pays