I am having a hard time getting this to work right. Maybe you have a answer....


I have several stores with a different set of items in each store, and all items are in at least two stores.

--I want to find the average sale price of all items carried in 'store A' when the sales location was another store and the date was in a certain range...so store <> 'store A' AND date BETWEEN '2013-xx-xx' AND '2013-xx-xx'...


I have tried several things but mostly looking similar to this:

--Select avg(sales price) FROM (SELECT * from sales where store <> 'store A' AND date BETWEEN '2013-xx-xx' AND '2013-xx-xx') AS sales WHERE item IN (SELECT item from sales where store = 'store A');



The goal is to compare average sales at store A with average sales of like items in different locations. I want to run for 30 stores to compare each location. Can anybody see a better way of getting my desired results. I feel like I might be taking the long road