Using a current row value into a subquery

Question

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.

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 

And here is the message i get: "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'."

Answer 1

Based on your description, this may be the query that you want:

select person, AVG(OrderTotal), COUNT(distinct orderId)
from (select Customer_id as person, Order_id, SUM(total) as OrderTotal
      from Orders
      group by Customer_Id, Order_Id
     ) o
group by person 

I say "may" because I would expect OrderId to be a unique key in the Orders table. So, the inner subquery wouldn't be doing anything. Perhaps you mean something like OrderLines in the inner query.

The reason your query fails is because of the correlation statement:

where Customer_Id = person

You intend for this to use the value from the outer query ("person") to relate to the inner one ("Customer_Id"). However, the inner query does not know the alias in the select clause of the outer one. So, "Person" is undefined. When doing correlated subqueries, you should always use table aliases. That query should look more like:

(select COUNT(o2.Order_Id) as timesSeen  
 from Orders o2 where  o2.Customer_Id=o.person 
 group by o2.Order_Id
)

Assuming "o" is the alias for orders in the outer query. Correlated subqueries are not needed. You should just simplify the query.

Answer 2

You can't use the name of your result column in a subquery. Name your subquery Orders table o and do WHERE Customer_id = o.Customer_Id. Preferably make up aliases for each of these tables so that they do not get mixed up.