title: SQL Server - Using a column alias in a subquery
body: <p>I have the following query which works fine with MySQL but refuses to work with SQL server:</p>
  
  <pre><code>SELECT table1.someField AS theField, 
         COUNT(table2.someField) / (SELECT COUNT(someField) FROM table1 WHERE someField = theField),
  FROM table1 LEFT JOIN table2 ON table1.someField = table2.someField
  </code></pre>
  
  <p>SQL Server doesn't seem to like the alias in the subquery. I've been told I need to use a CTE but I've never used them before. Is this correct?</p>