title: Using a current row value into a subquery
body: <p>I am trying to count how many times an order_Id appears in a subquery and then present it right next to the Average Value of each customer orders. Here is what I have tried.</p>
  
  <pre><code>select person ,AVG(orderTotal) as avgOrdersValue , timesSeen 
  from 
      (
  select  
  Customer_Id as person 
  ,Order_Id
  , SUM(total)as orderTotal 
  ,(select COUNT(Order_Id) as timesSeen  
  from Orders where  Customer_Id=person  group by Order_Id
      )
  from Orders group by Customer_Id  Order_Id order by person ) tablealias 
  group by person 
  </code></pre>
  
  <p>And here is the message i get: "<em>Msg 207, Level 16, State 1, Line 4
  Invalid column name 'person'.
  Msg 8155, Level 16, State 2, Line 10
  No column name was specified for column 4 of 'gg'.
  Msg 207, Level 16, State 1, Line 1
  Invalid column name 'timesSeen'.</em>"</p>