with T1 as
(
select distinct pays,valide_esp,count(distinct no_menage) as occur
from total.liste_site inner join total.v_liste_menage using(no_site,pays) inner join nomenc.trans_nomenclature_esp on(sp1_a=esp)

where  date_enquete_m is not null and engin_a is not null and (valide_esp not like 'Autres')
group by pays,valide_esp
)
,T2 as
(select distinct valide_esp,sum(occur) as tot1 from T1 group by valide_esp order by tot1 desc limit 8)
,T3 as
(select distinct pays,valide_esp,sum(occur) as occur
from T1 inner join T2 using(valide_esp)  group by pays,valide_esp )
,T4 as
(select distinct pays,sum(occur) as tot_occur from T3 group by pays)

select distinct pays as ABSCISSE,valide_esp as GROUPE,occur/ tot_occur*100 as TOTAL from T3
inner join T4 using(pays)
order by ABSCISSE