Requête |
with part1 as
(select distinct pays as ABSCISSE,strate,
case when lib_statut_site is null then 'Non réponse' else lib_statut_site end as lib_statut_site,count(no_site)*txa as TOTAL
from total.liste_site B inner join total.calcul_txa using(pays,strate)
where B.date_enquete is not null group by lib_statut_site,pays,txa,strate
),
part2 as
(
select distinct ABSCISSE,sum(TOTAL) as nbtot from part1 group by ABSCISSE
)
select distinct ABSCISSE,regroup_statut as GROUPE,100*sum(total)/nbtot as TOTAL from part1
inner join part2 using(ABSCISSE) inner join nomenc.regroup_statut_site using(lib_statut_site) group by ABSCISSE,GROUPE,nbtot
order by ABSCISSE
|