with autocons1 as
(

select distinct c.code_niveau0,no_site,pays,strate,
case when lieu like 'robinet%' then 'Robinets'
when lieu like 'puits%' then 'Puits/forages'
when lieu like 'eau%' then 'Lac-Mare-Rivière'
else 'Non réponse' end

 as eau,count(distinct no_menage)*txb as nb_menage from total.liste_site  inner join
  total.v_liste_menage using(no_site,pays) left join total.v_menage_eau using(no_menage,pays)
 inner join total.calcul_txb using(no_site,pays)
 left join (select distinct lib_pays,code_niveau0 from total.region_adm) c  on (upper(pays)=upper(C.lib_pays))
where date_enquete_m is not null
group by c.code_niveau0,pays,txb,no_site,eau,strate
)
,part2 as
(
select distinct code_niveau0 as LIEU,pays as ABSCISSE,eau as GROUPE,sum(nb_menage*txa*txc) as VAL from
autocons1 A
inner join total.calcul_txa B using(strate,pays)
inner join total.calcul_txc using(strate,pays)
group by code_niveau0,pays,eau order by ABSCISSE
)
select distinct ABSCISSE,GROUPE,100*(VAL/VALTOT) as TOTAL from
part2 inner join (select distinct ABSCISSE,sum(val) as VALTOT from part2 group by ABSCISSE) tmp using(ABSCISSE)
order by ABSCISSE