Requête |
with T1 as
(select distinct pays,avg(effectif_menage) as indic1 from total.v_liste_menage where effectif_menage>0 group by pays)
,T1_tot as
(select avg(effectif_menage) as indic1_tot from total.v_liste_menage where effectif_menage>0)
,T2 as
( select distinct pays ,strate, count(no_menage)*txa as nb_menage 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 )
,T2_P as
(select distinct pays,sum(nb_menage) as indic2 from T2
group by pays order by pays)
,T2_tot as
((select distinct '' as tri,pays,indic1,indic2,indic1*indic2 as indic3 from T1 inner join T2_P using(pays) )
union
(select distinct 'Ensemble UEMOA','',indic1_tot as indic1,nb_tot as indic2,indic1_tot*nb_tot as indic3
from (select sum(indic2) as nb_tot from T2_P) T2_P_P,T1_tot) )
select * from T2_tot order by tri,pays
|