title: Apply a Substr to a distinct result in oracle
body: <p>In Oracle, I need to do the average of a column (timeInmillis) from a join query that shows "duplicated" values (relative to the timeInmillis column from table1). I need to mantain the values from the join, but get the right result for the average.</p>
  
  <p>I'm trying to do something like this:</p>
  
  <pre><code>select AVG(SUBSTR(DISTINCT(concat(id1,timeInMillis)),LENGTH(id1)+1,LENGTH(CONCAT(id1,timeInMillis)))), someColumn, otherColumn 
  FROM table1 t1
  LEFT JOIN table2 t2  ON t1.id1 = t2.id1 group by somestuff,someotherStuff;
  </code></pre>
  
  <p>If I try to do this, I get: </p>
  
  <blockquote>
    <p>ORA-00936: missing expression</p>
  </blockquote>
  
  <p>This would be an example:</p>
  
  <p>Table1:</p>
  
  <pre><code>id1 timeInMillis otherColumn
  1       5            X
  2       15           X
  </code></pre>
  
  <p>Table2:</p>
  
  <pre><code>id2  id1 otherColumn
  --------------------
  1     1       X
  2     1       X
  3     2       X 
  </code></pre>
  
  <p>From my join I get a resultset like this:</p>
  
  <pre><code>id1 id2 timeInmillis moreColumns
  --------------------------------
  1    1      5           X
  1    2      5           X  
  2    3      15          X 
  </code></pre>
  
  <p>I need to get the average of 5 and 15 (with distinct id1), but I can't modify the where part of the sql (cause of the other values I'm getting)</p>
  
  <pre><code>My result should be:
  
  AVG(TIMEINMILLIS)    otherResults
  ----------------------------------  
  10                   'whatever'
  </code></pre>
  
  <p>Thanks in advance.</p>