Requête |
with T2 as (select pays,count(distinct no_site) as INDIC2 from total.liste_site A inner join nomenc.region_adm B on (A.pays=B.lib_pays)
where date_enquete is not null group by pays ) ,
T5 as ( select A.pays,count(distinct no_menage) as INDIC5
from total.liste_site A inner join total.v_liste_menage using (no_site,pays) inner join nomenc.region_adm B
on (A.pays=B.lib_pays) where date_enquete_m is not null group by A.pays ) ,
T1 as (select pays,count(distinct no_site) as INDIC1
from total.liste_site A inner join nomenc.region_adm B on (A.pays=B.lib_pays) group by pays )
,T4 as (select A.pays,count(distinct no_menage) as INDIC4 from total.liste_site A inner join total.v_liste_menage using (no_site,pays) inner join nomenc.region_adm B on (A.pays=B.lib_pays) group by A.pays )
,T3 as(select pays,count(distinct no_site) as INDIC3
from total.v_liste_menage A inner join nomenc.region_adm B on (A.pays=B.lib_pays) where date_enquete_m is not null group by pays )
,T6 as
(
select ''::char as regroupement,pays,INDIC1,INDIC2,INDIC3,INDIC4,INDIC5 from T1 inner join T2 using(pays) inner join T3 using(pays) inner join T4 using(pays) inner join T5 using(pays)
)
,T7 as
(select * from T6
union
select 'Ensemble UEMOA' as regroupement,'' as pays,sum(INDIC1) as INDIC1,sum(INDIC2) as INDIC2,sum(INDIC3) as INDIC3,sum(INDIC4) as INDIC4
,sum(INDIC5) as INDIC5 from T6
)
select * from T7 order by regroupement,pays
|