Requête |
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,indic1-indic2 as indic3 from T1_P inner join T2_P using(ABSCISSE) order by ABSCISSE)
,
T4 as ( select distinct pays as ABSCISSE,strate, count(no_menage)*txa as indic4 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_transfo+nb_femme_transfo+nb_homme_commer+nb_femme_commer)>0
group by strate,pays,txa )
,T4_P as(
select distinct ABSCISSE,round(sum(indic4)::numeric,0) as indic4 from T4
group by ABSCISSE order by ABSCISSE
)
,
T5 as ( select distinct pays as ABSCISSE,strate, count(no_menage)*txa as indic5 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)>0 and
(nb_homme_transfo+nb_femme_transfo+nb_homme_commer+nb_femme_commer)=0
and mecanicien is false and charpentier is false and commercant_met is false
and pratique_elevage is false and pratique_agri is false
group by strate,pays,txa )
,T5_P as(
select distinct ABSCISSE,round(sum(indic5)::numeric,0) as indic5 from T5
group by ABSCISSE order by ABSCISSE
)
select distinct ABSCISSE,indic1,indic2 as indic2,(indic2/indic1*100) as indic2_p,
indic3,(indic3/indic1*100) as indic3_p,indic4,(indic4/indic1*100) as indic4_p,indic5,(indic5/indic1*100) as indic5_p
from T1_P left join T2_P using(ABSCISSE) left join T3 using(ABSCISSE) left join T4_P using (ABSCISSE)
left join T5_P using(ABSCISSE)
|