CODEWITHSUNDEEP
sqlms-access-2010
Lotti
Lotti

Reputation: 1

SQL - get rows where max price is 50% greater than min price

I need to get the rows where the maximum price of a product in 2014 is at least 50% greater than the minimum price of that product.

I have a table Price History with 4 columns: shop ID, Product ID, date and price. Shops sell the same products at different prices. I don't know which query to use. Any help would be greatly appreciated!

Upvotes: 0

Views: 915

Answers (1)

Hogan
Hogan

Reputation: 70513

Just take it step by step.

First get the max and min price by product

 SELECT shopID, productID, MAX(price) as maxPrice, MIN(price) as minPrice
 FROM pricehistory
 WHERE year(date) = 2014
 GROUP BY shopID, productID

I suggest you run just this and understand it.

Then select the ones you want from this query

SELECT * 
FROM (
  SELECT shopID, productID, MAX(price) as maxPrice, MIN(price) as minPrice
  FROM pricehistory
  WHERE year(date) = 2014
  GROUP BY shopID, productID
) sub
WHERE minPrice * 1.5 < maxPrice

Upvotes: 1

Related Questions