with part1 as
(
select distinct A.region_niv1 as ABSCISSE,strate,
case when type_plan_eau is null then 'Non réponse' else type_plan_eau end
 as GROUPE,count(distinct no_site)*txa as TOTAL
from ci.pre_enquete_village A   inner join ci.liste_site  B using(no_enquete) inner join ci.calcul_txa using (strate)
inner join ci.v_site_plan_eau using(no_site)
group by GROUPE,A.region_niv1,txa,strate
)
select distinct ABSCISSE,GROUPE,sum(total) as TOTAL from part1 group by ABSCISSE,GROUPE
order by ABSCISSE