with T1 as
(
select distinct  c.code_niveau1  as LIEU,A.region_niv1,strate,
case when nationalite_actuelle is null then 'Non réponse' else nationalite_actuelle 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,strate,txb order by lieu desc
)
select distinct region_niv1 as ABSCISSE,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,txa order by ABSCISSE