Atlas Halieutique
Themes Stock
Name of the indicator Landings weight values
Definition Landings describes by year and countries
Objectives Main objective is to analyse each national fleet contribution to the landings
View
Calculation description We filter the FDI data for the specific species and list of statistical rectangles defined for the stock. Then we sum the landing values along the time serie and we keep 5 major countries. The we sum the rest of the landings in the "Other" strata.
Reading guide For each year we get the landings for 5 major countries (Other is used to compile landings of other countries if needed)
Data sources FDI landings
Comments
Query with part1 as ( select distinct year_d as ABSCISSE,country_name as GROUPE,sum(landings)/1000 as TOTAL from landing_rect_sd inner join def_stocks_area using (species,rectangle) left join countries using(country) where fishstock like 'bss.27.4bc7ad–h' and annexe not like 'MED' group by ABSCISSE,GROUPE order by ABSCISSE ) ,selection as ( select distinct GROUPE,sum(TOTAL) as tot from part1 group by GROUPE order by tot DESC limit 5 ) ,regroupement as ( select distinct ABSCISSE,case when selection.GROUPE is null then 'Other' else selection.GROUPE end as GROUPE,part1.TOTAL from part1 left join selection using(GROUPE) ) select distinct ABSCISSE,GROUPE, sum(TOTAL) as TOTAL from regroupement group by ABSCISSE,GROUPE order by ABSCISSE,GROUPE
Retour à la fiche