I broke this down into multiple pieces and then put them back together.
First, I got the average price for each product_id:
SELECT product_id, AVG(price) AS averagePrice
FROM myTable
GROUP BY product_id;
The following makes the assumption that a product_id cannot appear at a store more than once. So I've joined the tables together, so I can see what the store charges next to the average price for that item:
SELECT m.product_id, m.store_id, m.price, t.averagePrice
FROM myTable m
JOIN(
SELECT product_id, AVG(price) AS averagePrice
FROM myTable
GROUP BY product_id) t ON t.product_id = m.product_id;
Once I had that, I was able to take the difference between averagePrice and price, divide by average price and multiply by 100 like this:
SELECT m.product_id, m.store_id, (100 * ((m.price - t.averagePrice) / t.averagePrice)) AS difference
FROM myTable m
JOIN(
SELECT product_id, AVG(price) AS averagePrice
FROM myTable
GROUP BY product_id) t ON t.product_id = m.product_id;
It worked for me in SQL Fiddle.
EDIT
To get the average price difference per store (for all items) I believe you can just take the above, and by averaging the stores price difference on each individual item, you'll get the average price difference for that store, like this:
SELECT store_id, AVG(difference) AS averagePriceDifference
FROM(
SELECT m.product_id, m.store_id, (100 * ((m.price - t.averagePrice) / t.averagePrice)) AS difference
FROM myTable m
JOIN(
SELECT product_id, AVG(price) AS averagePrice
FROM myTable
GROUP BY product_id) t ON t.product_id = m.product_id) t
GROUP BY store_id;
Here it is in Fiddle.
EDIT 2
Again, I will rework this in pieces and try and put it back together. I know I will need a subquery to get the number of stores (so I know if a product is sold at each store) and I can use this:
SELECT COUNT(distinct store_id) AS storecount
FROM myTable;
Now, I can use that as a subquery to get products sold at every store. I can group by product_id and amount, so that if every store has the item at amount 1, and every store has item at amount 2, it will show up each time.
SELECT product_id, amount
FROM myTable
GROUP BY product_id, amount
HAVING COUNT(distinct store_id) = (SELECT COUNT(distinct store_id) FROM myTable);
I can add to the above to get the average price of each item for that amount:
SELECT product_id, amount, AVG(price) AS averagePriceForAmount
FROM myTable
GROUP BY product_id, amount
HAVING COUNT(distinct store_id) = (SELECT COUNT(distinct store_id) FROM myTable);
Once I have that, I can calculate the average price difference for each store using the same method I used earlier, like this:
SELECT m.store_id, m.product_id, m.amount, (100 * ((m.price - t.averagePriceForAmount) / t.averagePriceForAmount)) AS differenceForItemAndAmount
FROM myTable m
JOIN(
SELECT product_id, amount, AVG(price) AS averagePriceForAmount
FROM myTable
GROUP BY product_id, amount) t ON t.product_id = m.product_id AND t.amount = m.amount
GROUP BY m.store_id;
This will return the store, product_id, the amount of the product, and the store's difference from the average price for that product for that amount. If you want the average price difference for the store on all items, try this:
SELECT store_id, AVG(differenceForItemAndAmount) AS averageDifferenceForStore
FROM(
SELECT m.store_id, m.product_id, m.amount, (100 * ((m.price - t.averagePriceForAmount) / t.averagePriceForAmount)) AS differenceForItemAndAmount
FROM myTable m
JOIN(
SELECT product_id, amount, AVG(price) AS averagePriceForAmount
FROM myTable
GROUP BY product_id, amount) t ON t.product_id = m.product_id AND t.amount = m.amount
GROUP BY m.store_id) t
GROUP BY store_id;
Again, this will only include items sold at every store, that has the same amount discount at every store.
