SQL simple select plus average of other lines
Posted on 2013-05-12
I'm wondering how I can do something directly in the SQL statement without having to do two SQL statements and then have to loop through in code and match it together.
I want to select a list of all open orders, the name of the customer, the amount on the order - and the difficult part (for me) - the average order amount for that customer for orders that are closed.
So in mock up language it becomes something like:
SELECT [OrderNumber], [Customer], [Amount], (AVG([Amount] where Customer = same as this line and [status] = 'closed') AS "Avg Closed Orders Amount"
WHERE [status] = 'open'
Is this possible to do directly in the SQL statement?
Or do I need to first pull all open orders. Then pull the average for closed orders grouped by customer, and then in programming code combine the two?
I can see that there is one very big difference between the two parts of the query. The first part is list per order number. (No group by or select distinct.) The average is grouped by customer, and if a customer has 5 open orders the same average will show up 5 times. And I think that's a problem, but I'm not sure if there are any ways around it.