Requête |
with T1 as ( select distinct ci.pre_enquete_village.region_niv1 as ABSCISSE,strate,count(no_menage)*txa as
A from ci.pre_enquete_village inner join ci.liste_site using (no_enquete) inner join ci.calcul_txa using (strate)
inner join ci.v_liste_menage
using (no_site)
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 ci.pre_enquete_village.region_niv1,txa,strate ) ,
T2 as ( select distinct ci.pre_enquete_village.region_niv1 as ABSCISSE,strate,count(no_menage)*txa as B
from ci.pre_enquete_village inner join ci.liste_site inner join ci.calcul_txa using (strate)
using (no_enquete) inner join ci.v_liste_menage using (no_site) 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 ci.pre_enquete_village.region_niv1,txa,strate ),
T2_prime as ( select distinct ci.pre_enquete_village.region_niv1 as ABSCISSE,strate, count(no_menage)*txa as B_prime
from ci.pre_enquete_village inner join ci.liste_site inner join ci.calcul_txa using (strate)
using (no_enquete) inner join ci.v_liste_menage using (no_site) 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 and
not(mecanicien is true or charpentier is true or commercant_met is true))
group by ci.pre_enquete_village.region_niv1,txa,strate),
T3 as ( select distinct ci.pre_enquete_village.region_niv1 as ABSCISSE,strate, count(no_menage)*txa as C
from ci.pre_enquete_village
inner join ci.liste_site inner join ci.calcul_txa using (strate) using (no_enquete) inner join ci.v_liste_menage
using (no_site) where (nb_homme_peche+nb_femme_peche)>0 and
(nb_homme_transfo+nb_femme_transfo+nb_homme_commer+nb_femme_commer)=0
and not(mecanicien is true or charpentier is true or commercant_met is true or pratique_elevage is true
or pratique_agri is true) group by strate,ci.pre_enquete_village.region_niv1,txa )
select * from
(select
ABSCISSE,'Polyvalents secteurs primaires incluant pêche' as GROUPE, sum(B) as TOTAL from T2 group by ABSCISSE
union
select ABSCISSE,'Eleveur Agriculteur strict' as GROUPE, sum(B_prime) as TOTAL from T2_prime group by ABSCISSE
union
select ABSCISSE,'Secteur pêche limité à la capture' as GROUPE, sum(C) as TOTAL from T3 group by ABSCISSE
union
select ABSCISSE,'Secteur pêche non limité à capture' as GROUPE, sum(A)-(sum(B)+sum(C)+sum(B_prime)) as TOTAL
from T1 inner join T2 using(ABSCISSE,strate) inner join T2_prime using(ABSCISSE,strate) inner join T3 using(ABSCISSE,strate) group by ABSCISSE) Rtot
order by TOTAL DESC
|