title: SQL: aggregate function in select statement with a join involving group by clause
body: <p>I have two tables with following attributes:</p>
  
  <pre><code>t1                               t2
  source    domain     date           rank     domain     date 
  ---       ---        ---            ---      ---        ---
  A        google    2008-10-24        1      google    2007-10-20
  A        facebook  2010-05-17        1      google    2009-02-21
  B        google    2007-05-14        2      facebook  2008-04-13
  B        google    2012-01-05        2      facebook  2012-09-12
  B        facebook  2014-03-23        2      facebook  2015-04-01  
  </code></pre>
  
  <p>I am trying to find the date difference b/w the first(earliest) occurrence of each <code>domain</code> in <code>t2</code> with their first occurrence (based on <code>date</code>) in <code>t1</code>.</p>
  
  <p>The following query is giving me the required result:</p>
  
  <pre><code>select t2.domain, datediff(min(t1.date),min(t2.date)) 
  from t1 join t2 
  on t1.domain = t2.domain 
  group by t2.domain
  </code></pre>
  
  <p>Now, if I want to compute the average of date difference across all domains (only for their first occurrence), this part gives an error in select statement (rest of the query is same as above):<br><br>
       <code>select avg(datediff(min(t1.date),min(t2.date))) ...</code><br><br>
  Any idea how to correct the error (Invalid use of group function) here?<br>
  Also, I want to compute the average date difference group by <code>source</code>. Such that the output should be like: <br></p>
  
  <pre><code>source     avg_datediff 
  ---        ---
  A          some_value 
  B          some_value 
  </code></pre>
  
  <p>Just like above, for multiple same domains in a given source, I need to consider the date of earliest domain for that source only, when computing avg date difference. </p>