Requête |
with valeur_nombre as
(select distinct c.code_niveau1 as LIEU,A.region_niv1 as ABSCISSE,replace(replace(type_engin,'Filet dérivant ',''),'Filet dormant ','') as GROUPE,sum(nombre*txa*txb*txc) as VAL
from
ci.pre_enquete_village A inner join ci.liste_site using (no_enquete)
inner join ci.calcul_txa using (strate)
inner join ci.calcul_txc using (strate)
inner join ci.calcul_txb using(no_site)
inner join ci.v_liste_menage using (no_site)
left join ci.v_menage_poss_engin using(no_menage)
left join (select distinct lib_niveau1,code_niveau1 from ci.region_adm) c on (upper(A.region_niv1)=upper(C.lib_niveau1))
where type_engin like 'Filet d%' and date_enquete_m is not null
group by LIEU,A.region_niv1,GROUPE order by VAL desc
)
select distinct LIEU,ABSCISSE,GROUPE,round((val/somtot*100)::numeric,2) as VAL
from valeur_nombre inner join (select distinct ABSCISSE,sum(val) as somtot from valeur_nombre group by ABSCISSE) tot
using (ABSCISSE)
order by LIEU
|