with T1 as
(
select distinct  c.code_niveau1  as LIEU,A.region_niv1,strate,
case when groupe_ethnique_chef_menage is null then 'Non réponse' else  groupe_ethnique_chef_menage end as GROUPE,no_site,count(no_menage)*txb as total
from
ci.pre_enquete_village A 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 (select distinct  lib_niveau1,code_niveau1 from ci.region_adm) c on (upper(A.region_niv1)=upper(C.lib_niveau1))
where date_enquete_m is not null
group by no_site,LIEU,A.region_niv1,GROUPE,txb,strate order by lieu desc
)
,T2 as
(select distinct region_niv1 as ABSCISSE,case when GROUPE is not NULL then GROUPE else '' 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)
group by ABSCISSE,GROUPE  order by ABSCISSE)
,T3 as
(select distinct GROUPE,sum(TOTAL) as TOTAL from T2 group by GROUPE order by TOTAL DESC LIMIT 6)

select distinct ABSCISSE,GROUPE,T2.TOTAL from T2 inner join T3 using(GROUPE)
union
select distinct ABSCISSE,'autres',sum(T2.TOTAL) from T2 where GROUPE not in (select distinct GROUPE from T3)
group by ABSCISSE order by ABSCISSE