with calcul_prix as
(
with nb_moy as
(select distinct pays,strate,type_embarcation as GROUPE,avg(nombre) as moy_nb
 from
total.liste_site
inner join total.v_liste_menage using (pays,no_site)
inner join  total.v_menage_type_embarcation using(pays,no_menage)
where (nombre between 1 and 10) and type_embarcation like 'Pirogue de pêche non motorisée'
group by pays,strate,type_embarcation
)
,prix_moy as
(select distinct pays,strate,type_embarcation as GROUPE,avg(prix_paye) as moy_prix
 from
total.liste_site
inner join total.v_liste_menage using (pays,no_site)
inner join  total.v_menage_type_embarcation using(pays,no_menage)
where prix_paye>0  and prix_paye<2500000 and type_embarcation like 'Pirogue de pêche non motorisée'
group by pays,strate,type_embarcation )
,duree_moy as
(select distinct pays,strate,type_embarcation as GROUPE,avg(duree_vie) as moy_duree
 from
total.liste_site
inner join total.v_liste_menage using (pays,no_site)
inner join  total.v_menage_type_embarcation using(pays,no_menage)
where  duree_vie>0 and duree_vie<40 and type_embarcation like 'Pirogue de pêche non motorisée'
group by pays,strate,type_embarcation )
select distinct pays,strate,'' as GROUPE,round(moy_nb*moy_prix/moy_duree) as TOTAL,
round(moy_nb,2) as nb_moy,round(moy_prix) as prix_moy,moy_duree as duree_moy
from nb_moy inner join prix_moy using (pays,strate,GROUPE) inner join duree_moy using (pays,strate,GROUPE)

)
,nb_menages as
( select distinct pays,strate,
count(no_menage)*txa as nb_men from total.liste_site inner join total.calcul_txa using (pays,strate)
  inner join total.v_liste_menage using (pays,no_site) inner join calcul_prix using(pays,strate) where
(nb_homme_peche+nb_femme_peche+nb_homme_transfo+nb_femme_transfo+nb_homme_commer+nb_femme_commer)>0


or (pratique_elevage is true or pratique_agri is true)
or (mecanicien is true or charpentier is true or commercant_met is true) group by strate,pays,txa )
,
cout_engin as
(select distinct pays,strate,avg(depense_engin_peche) as total_engin
from
total.liste_site
inner join total.v_liste_menage using (pays,no_site)
where depense_engin_peche>0 and depense_engin_peche<1500000
group by pays,strate)
,
final as
(select distinct pays,sum(total*nb_men)/nb_men_tot as cout_moy,sum(total_engin*nb_men)/nb_men_tot as cout_moy_eng from calcul_prix inner join nb_menages using(pays,strate)
inner join cout_engin using(pays,strate) inner join (select distinct pays,sum(nb_men) as nb_men_tot from nb_menages group by pays) R1 using (pays) group by pays,nb_men_tot
order by PAYS
)
select *,cout_moy+cout_moy_eng as total  from final order by pays