Requête |
with total as
(select distinct pays,strate,count(distinct no_menage)*txa as nb_tot from
total.liste_site B
inner join total.calcul_txa using (pays,strate) inner join total.v_liste_menage using(pays,no_site)
where B.date_enquete is not null
group by pays,txa,strate)
,osp as
(select distinct pays,strate,count(distinct no_menage)*txa as TOTAL from
total.liste_site B inner join total.calcul_txa using (pays,strate) inner join total.v_osp using(pays,no_site)
inner join total.v_liste_menage using(pays,no_site)
group by pays,txa,strate)
,final as
(select distinct pays ,sum(TOTAL) as nb_menage_osp,sum(nb_tot) as nb_menage_tot,sum(TOTAL)/sum(nb_tot)*100 as TOTAL
from total inner join osp using(pays,strate)
group by pays order by pays)
select ''::text as ens,pays,TOTAL from final
union
select 'Ensemble UEMOA'::text as ens ,''::text as pays,sum(nb_menage_osp)/sum(nb_menage_tot)*100 as TOTAL from final
|