Requête |
with nb_site_tot as
(
select pays,count(distinct no_site) as nb_site from total.liste_site group by pays
)
,commodites_site as
(
select distinct PAYS,commodites,sum(nb_comm)/nb_site*100 as nb_comm from
(select distinct PAYS,strate,reg.regroup as commodites,count(distinct no_site)*txa as nb_comm from total.liste_site
inner join total.calcul_txa using (pays,strate)
inner join total.v_commodites_debarcadere using(pays,no_site) inner join nomenc.commodites_deb_regroup reg using(commodites)
where proximite in ('Sur place','A proximité (<10km)') group by pays,reg.regroup,txa,strate) tmp inner join nb_site_tot using(pays)
group by PAYS,commodites,nb_site
)
,commodites_site_uemoa as
(
select distinct commodites,sum(nb_comm)/nb_site*100 as nb_comm from
(select distinct PAYS,strate,reg.regroup as commodites,count(distinct no_site)*txa as nb_comm from total.liste_site
inner join total.calcul_txa using (pays,strate)
inner join total.v_commodites_debarcadere using(pays,no_site) inner join nomenc.commodites_deb_regroup reg using(commodites)
where proximite in ('Sur place','A proximité (<10km)') group by pays,reg.regroup,txa,strate) tmp,(select sum(nb_site) as nb_site from nb_site_tot) R1
group by commodites,nb_site
)
,tot as
((select '' as ens,*
from
(select PAYS,nb_comm as Indic1 from commodites_site where commodites in ('Facilité pour frais') ) T1
left join
(select PAYS,nb_comm as Indic2 from commodites_site where commodites in ('Prestataires de services')) T2 using (PAYS)
left join
(select PAYS,nb_comm as Indic3 from commodites_site where commodites in ('Matériel de pêche')) T3 using (PAYS)
left join
(select PAYS,nb_comm as Indic4 from commodites_site where commodites in ('Amenagement du débarcadère') ) T4 using (PAYS)
left join
(select PAYS,nb_comm as Indic5 from commodites_site where commodites in ('Falicités pour transformer') ) T5 using (PAYS)
left join
(select PAYS,nb_comm as Indic6 from commodites_site where commodites in ('Hygiène') ) T6 using (PAYS)
)
union
(
select 'Ensemble UEMOA' as ens,*
from
(select '' as PAYS,nb_comm as Indic1 from commodites_site_uemoa where commodites in ('Facilité pour frais')) T1
,
(select nb_comm as Indic2 from commodites_site_uemoa where commodites in ('Prestataires de services')) T2
,
(select nb_comm as Indic3 from commodites_site_uemoa where commodites in ('Matériel de pêche')) T3
,
(select nb_comm as Indic4 from commodites_site_uemoa where commodites in ('Amenagement du débarcadère')) T4
,
(select nb_comm as Indic5 from commodites_site_uemoa where commodites in ('Falicités pour transformer'))T5
,
(select nb_comm as Indic6 from commodites_site_uemoa where commodites in ('Hygiène')) T6
)
)
select * from tot order by ens,pays
|