title: SQL query aggregate function in WHERE clause
body: <p>I got some question regarding MSSQL statement. So basically this is my SQL query:</p>
  
  <pre><code>SELECT table1.col1, avg(datediff(dd, table2.date, table3.date)) as avg_date
  INNER JOIN table2
  INNER JOIN table3
  WHERE avg_date &lt;= ALL
  (SELECT table1.col1, avg(datediff(dd, table2.date, table3.date)) as avg_date
  INNER JOIN table2
  INNER JOIN table3
  GROUP BY table1.col1);
  </code></pre>
  
  <p>What I am trying to do is inside the subquery, I am getting the list of average date group by each user. The example data returned are (userName, avg_date):</p>
  
  <pre><code>user1  10
  user2  20
  user3  20
  </code></pre>
  
  <p>Then after that, from the query outside, I need to find the minimum of the average date returned from the sub query. However, by doing this, I am getting and error message as from the query outside, I am comparing 1 column only whereby the subquery is returning 2 columns.</p>
  
  <p>The error message is <code>An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference</code></p>
  
  <p>However, inside my subquery I need the GROUP BY each user so I cannot simply SELECT avg.</p>
  
  <p>Any ideas how to fix this?</p>
  
  <p>Thanks in advance.</p>