with autocons1 as
(
select distinct c.code_niveau0  as LIEU,no_site,strate,pays,
case when type_instruction is null then 'Non réponse' else type_instruction end as type_instruction  ,count(distinct no_menage)*txb as nb_menage
from total.liste_site
 inner join total.v_liste_menage using(no_site,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 LIEU,pays,txb,no_site,type_instruction,strate
)
,autocons2 as
(
select distinct LIEU,pays,type_instruction,sum(nb_menage)*txa*txc as nb_auto from
autocons1 A inner join total.calcul_txa B using(strate,pays)
inner join total.calcul_txc  using(strate,pays)
group by LIEU,pays,txa,type_instruction,txc)
,nb_menage_region as
(select distinct pays,sum(nb_auto) as nb_menage_tot from autocons2 group by pays)
,final as
(
select distinct pays ,type_instruction as GROUPE,(sum(nb_auto)/nb_menage_tot*100) as VAL from autocons2
inner join nb_menage_region using(pays)
group by pays,nb_menage_tot,GROUPE,lieu order by pays)

,final2 as
(select * from
(select distinct pays,sum(val) as indic1 from final where GROUPE in ('Autres','Ecole coranique') group by pays) R1
inner join
(select distinct pays,sum(val) as indic2 from final where GROUPE in ('Ecole francaise/ portugaise / franco-arabe') group by pays) R2 using(pays))
,lecture as
(
with autocons1 as
(

select distinct c.code_niveau0  as LIEU,strate,no_site,pays,
case when competence_lecture=-1 then 'Lecture acquise'
when competence_lecture=0 then 'Lecture non acquise'
else 'Non réponse' end

 as lecture,count(distinct no_menage)*txb as nb_menage from total.liste_site
 inner join total.v_liste_menage using(no_site,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 LIEU,pays,txb,no_site,lecture,strate
)
,autocons2 as
(
select distinct LIEU,pays,strate,lecture,sum(nb_menage)*txa*txc as nb_auto from
autocons1 A inner join total.calcul_txa B using(strate,pays)
inner join total.calcul_txc using(strate,pays)
group by LIEU,pays,txa,lecture,txc,strate
)
,nb_menage_region as
(
SELECT DISTINCT pays,sum(nb_auto) as nb_menage_tot from
           autocons2 GROUP BY pays
 )
select distinct pays,(sum(nb_auto)/nb_menage_tot)*100 as indic4 from autocons2
inner join nb_menage_region using(pays) where lecture like 'Lecture acquise'
group by pays,nb_menage_tot order by pays
)
select distinct pays,indic1,indic2,indic1+indic2 as indic3,indic4 from final2 inner join lecture using(pays)