title: postgresql multiple Common Table Expressions
body: <p>I have to tables : <em>mutation</em> and <em>ann_commune</em>. The colums <em>code_postal</em> and <em>id_commune</em> are in <em>ann_commune</em>, all the other ones in <em>mutation</em>.</p>
  <p>I need to get together the average for the top 3 values of several departments</p>
  <pre><code>         WITH six as ( 
      SELECT commune as commune_six, AVG(valeur_fonciere) as moyenne_six 
      FROM mutation
          LEFT JOIN ann_commune    on mutation.id_commune = ann_commune.id_commune
        WHERE code_postal LIKE '06%'
        GROUP BY commune_six
        ORDER BY moyenne_six DESC
        LIMIT 3
      ),
      treize as ( 
      SELECT commune as commune_treize, AVG(valeur_fonciere) as moyenne_treize 
      FROM mutation
          LEFT JOIN ann_commune    on mutation.id_commune = ann_commune.id_commune
        WHERE code_postal LIKE '13%'
        GROUP BY commune_treize
        ORDER BY moyenne_treize DESC
        LIMIT 3
      )
      
       select  AVG(moyenne_six)::NUMERIC(7,0) as Moyenne_top3_06,
               AVG(moyenne_treize)::NUMERIC(7,0) as Moyenne_top3_13
       from six, treize 
  </code></pre>
  <p>I am sure there is a way to create a nicer quode, because I have to do the same with 10 averages. I did not find the solutions, but I am sure it exists.
  Thank you in advance :).</p>