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 )
,T1_P as
(select distinct pays as ABSCISSE,sum(indic1) as indic1 from T1
group by ABSCISSE order by ABSCISSE)
,
T2 as ( select distinct pays as ABSCISSE,strate, count(no_menage)*txa as indic2 from total.liste_site inner join total.calcul_txa using (strate,pays)
inner join total.v_liste_menage using (no_site,pays) where
(pratique_elevage is true or pratique_agri is true)
and ((
nb_homme_peche+nb_femme_peche+nb_homme_transfo+nb_femme_transfo+nb_homme_commer+nb_femme_commer)>0
or (mecanicien is true or charpentier is true or commercant_met is true))
group by strate,pays,txa )
,T2_P as
(select distinct ABSCISSE,round(sum(indic2)::numeric,0) as indic2 from T2
group by ABSCISSE order by ABSCISSE)
,T3 as
(select distinct ABSCISSE,'Pêche + agriculture/élévage' as GROUPE,indic2 as TOTAL from T2_P
union
select distinct ABSCISSE,'Pêche sans agriculture/élévage' as GROUPE ,indic1-indic2 as TOTAL from T1_P inner join T2_P using(ABSCISSE)
)
select * from T3 order by ABSCISSE