with
R1 as
(
select distinct A.region_niv1 as ABSCISSE,strate,count(no_site)*txa as nb_total
from ci.pre_enquete_village A   inner join ci.liste_site  B using(no_enquete) inner join ci.calcul_txa using (strate)
where B.date_enquete is not null
group by A.region_niv1,txa,strate
),
R2 as
(select distinct A.region_niv1 as ABSCISSE,strate,(count(no_site)*txa) as nb_pieces
from ci.pre_enquete_village A   inner join ci.liste_site  B using(no_enquete) inner join ci.calcul_txa using (strate)
where pieces_eau_artificielles=-1
group by A.region_niv1,txa,strate)
select distinct ABSCISSE,'' as GROUPE,(sum(nb_pieces)/sum(nb_total))*100 as TOTAL
from R1 left join R2 using (ABSCISSE,strate)
group by ABSCISSE
order by ABSCISSE