gistfile1.txt

latest table:
ItemID int (11), // ID of the product / item
ShopID int (11), // ID of the store that sells it
Buy int (11), // price the items are sold for (as in, price the customer buys it for)
Sell int (11), // price the items are bought for (as in, price the customer gets for it, if he sells it in this store)
Count int (11), // amount of items sold/bought (the prices are with the amount, if the price is 10 and the amount 16 it means the 16 cost 10, not that 1 costs 10)

Primary Key (ItemID,ShopID)

Problem: trying to get entries where the price the item can be bought at is lower than the price it can be sold for, at any store
(e.g. if an item of ID 11 can be bought for 10 in Store A and sold for 40 in Store B it should list that )


SELECT *
FROM latest
WHERE
    latest.Buy/latest.Count <
        (SELECT Max(NULLIF(latest.Sell,0)/latest.Count)
        FROM latest as latest2
        WHERE latest.ItemID = latest2.ItemID)
AND latest.Buy > 0