Requête |
with part1 as
(
select distinct pays 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 total.liste_site B inner join total.calcul_txa using (strate,pays)
inner join total.v_site_plan_eau using(no_site,pays)
group by GROUPE,pays,txa,strate
),
part2 as
(select distinct ABSCISSE,GROUPE,sum(total) as TOTAL from part1 group by ABSCISSE,GROUPE
order by ABSCISSE)
select distinct ABSCISSE,GROUPE,100*TOTAL/nbtot as TOTAL from
part2 inner join (select distinct ABSCISSE,sum(TOTAL) as nbtot from part2 group by ABSCISSE) cal_tot using(ABSCISSE)
order by ABSCISSE
|