Query |
with part1 as
(
select distinct year_d as ABSCISSE,reg_gear as GROUPE,sum(landings)/1000 as TOTAL from landing_rect_sd
inner join def_stocks_area using (species,rectangle)
where fishstock like 'nep.fu.2021' 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
|