title: Aggregation for join ON clause
body: <p>I have a table <code>item_table</code> like this:</p>
  
  <pre><code>item   age
  --------------    
  1      1 
  1      6 
  2      2    
  </code></pre>
  
  <p>I have the other table <code>price_table</code> like this:</p>
  
  <pre><code>item    pricetype    price
  --------------------------    
  1       O             5
  1       P             6
  1       V             7
  2       O             8
  2       P             9
  2       V             10
  </code></pre>
  
  <p>So, I want to inner join above two tables.</p>
  
  <pre><code>select *
  from item_table i
  inner join price_table p
  on ...
  </code></pre>
  
  <p>There are some conditions about the <code>on</code>:</p>
  
  <ol>
  <li>if <strong>the average of age</strong> of an item is bigger than <code>3</code>, then I do: <code>inner join price_table on pricetype = 'O' or pricetype = 'P'</code></li>
  <li>If not, then I do: <code>inner join price_table on pricetype = 'O' or pricetype = 'P' or pricetype = 'V'</code></li>
  </ol>
  
  <p>So there are conditions for <code>on</code> conditions.</p>
  
  <p>I then write the query like this:</p>
  
  <pre><code>select i.item, i.type, p.pricetype, p.price
  from item_table i
  inner join price_table p on i.item = p.item 
      and (avg(i.age) &gt;= 3 and p.pricetype in ('O', 'P'))
          or (avg(i.age) &lt; 3 and p.pricetype in ('O', 'P', 'V'))
  </code></pre>
  
  <p>The error is given: <code>An aggregate cannot appear in an ON clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference.</code></p>
  
  <p>I can't move the <code>avg</code> to <code>Having</code> because other conditions are depending on the <code>avg</code>.</p>
  
  <p><strong>How can I write the select query?</strong></p>