with T1 as
(
select distinct ci.pre_enquete_village.region_niv1,strate,no_site,regroup as GROUPE,count(no_menage)*txb as total from
ci.pre_enquete_village  inner join ci.liste_site using (no_enquete) 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) inner join nomenc.engin2_regroup on (type_engin=libel_engin)
where nombre<20 and date_enquete_m is not null
group by no_site,ci.pre_enquete_village.region_niv1,GROUPE,strate,txb order by total desc
)
,selection as
(
select distinct GROUPE,sum(total) as total_sel from T1 group by GROUPE order by total_sel DESC limit 5
)
,pris_selection as
(select distinct region_niv1 as ABSCISSE,
case when (selection.GROUPE isnull or selection.GROUPE like 'Autre%') then 'Autre' else GROUPE end AS GROUPE,sum(total)*txa*txc as TOTAL from T1
inner join ci.calcul_txa using (strate)
inner join ci.calcul_txc using (strate)
left join selection using(GROUPE)
group by ABSCISSE,GROUPE,txa,txc,selection.GROUPE
)
select distinct ABSCISSE,GROUPE,sum(TOTAL) as TOTAL from pris_selection
group by ABSCISSE,GROUPE order by ABSCISSE,TOTAL