Requête |
with T1 as ( select distinct ci.pre_enquete_village.region_niv1 as ABSCISSE,strate,
count(no_menage)*txa as indic1 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 strate,ci.pre_enquete_village.region_niv1,txa ) ,
T2 as ( select distinct ci.pre_enquete_village.region_niv1 as ABSCISSE,strate, count(no_menage)*txa as indic2
from ci.pre_enquete_village
inner join ci.liste_site using (no_enquete) inner join ci.v_liste_menage using (no_site) inner join ci.calcul_txa using (strate) 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,ci.pre_enquete_village.region_niv1,txa ) ,
T3 as (
select distinct ci.pre_enquete_village.region_niv1 as ABSCISSE,strate, count(no_menage)*txa as indic3 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_transfo+nb_femme_transfo+nb_homme_commer+nb_femme_commer)>0 group by ci.pre_enquete_village.region_niv1,txa,strate ) ,
T4 as ( select distinct ci.pre_enquete_village.region_niv1 as ABSCISSE,strate, count(no_menage)*txa as indic4
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
(mecanicien is true or charpentier is true or commercant_met is true) group by ci.pre_enquete_village.region_niv1,strate,txa ) ,
T5 as (
select distinct ci.pre_enquete_village.region_niv1 as ABSCISSE,strate, count(no_menage)*txa as indic5
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)>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 ci.pre_enquete_village.region_niv1,txa,strate )
,total as
(
select distinct 'Total' as ordre,'' as ABSCISSE,sum(indic1) as indic1 ,sum(indic2) as indic2 ,sum(indic3) as indic3,
sum(indic4) as indic4,sum(indic5) as indic5
from T1 left join T2 using (ABSCISSE,strate) left join T3 using (ABSCISSE,strate) left join T4 using (ABSCISSE,strate) left join T5 using (ABSCISSE,strate)
)
select * from
(
select distinct '' as ordre,ABSCISSE,sum(indic1) as indic1 ,sum(indic2) as indic2 ,sum(indic3) as indic3,
sum(indic4) as indic4,sum(indic5) as indic5
from T1 left join T2 using (ABSCISSE,strate) left join T3 using (ABSCISSE,strate) left join T4 using (ABSCISSE,strate) left join T5 using (ABSCISSE,strate)
group by ABSCISSE,ordre
union
select * from total) plustot
order by ordre,ABSCISSE
|