SELECT intitule_competence, avg( n.id_niveau_competence ) AS moy, count( n.id_niveau_competence ) AS nb
FROM offre o, competenceoffre co, competence c, niveau_competence n
WHERE o.id_offre = co.id_offre
AND co.id_competence = c.id_competence
AND co.id_offre <>1
AND c.id_niveau_competence = n.id_niveau_competence
AND actif_offre = '1'
AND intitule_competence
IN (
  SELECT intitule_competence
  FROM competenceoffre co, competence c
  WHERE co.id_competence = c.id_competence
  AND co.id_offre =1
)
GROUP BY intitule_competence
ORDER BY intitule_competence