with nb_menage_pays as
(
with T1 as ( select distinct pays ,strate, count(no_menage)*txa as indic1 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 )
select distinct pays as ABSCISSE,'' as GROUPE,sum(indic1) as nb_menage from T1
group by ABSCISSE order by ABSCISSE
)
,final as
(
select distinct ABSCISSE,'Transformation' as GROUPE,avg(total/nb_menage)/1000 as total from total.gains_transformation
 inner join nb_menage_pays using(ABSCISSE) group by ABSCISSE
 union
select distinct ABSCISSE,'Capture' as GROUPE,avg(total/nb_menage)/1000 as total from total.gains_capture
 inner join nb_menage_pays using(ABSCISSE) group by ABSCISSE
union
select distinct ABSCISSE,'Commerce' as GROUPE,avg(total/nb_menage)/1000 as total from total.gains_commerce
 inner join nb_menage_pays using(ABSCISSE) group by ABSCISSE
)
select * from final order by ABSCISSE,GROUPE