I have a problem where we have different quantities of a product that share the same price. I want to find the products that share the same price for different quantities.
E.G. We have output Table Name: Goods
| Product | Quantity | Price |
|---|---|---|
| A | 5 | 1.99 |
| A | 10 | 1.99 |
| A | 15 | 12.99 |
| B | 5 | 3.99 |
| B | 10 | 7.99 |
| B | 15 | 3.99 |
I want a result that only shows rows where the quantity is different and the price is the same.
E.G.
| Product | Quantity | Price |
|---|---|---|
| A | 5 | 1.99 |
| A | 10 | 1.99 |
| B | 5 | 3.99 |
| B | 10 | 3.99 |
How am I able to get this output?
CodePudding user response：
Perfect example for exists
SELECT *
FROM Goods a
WHERE EXISTS (SELECT *
FROM Goods b
WHERE a.Product = b.Product AND a.Price = b.Price AND a.Quantity <> b.Quantity);
CodePudding user response：
You can use a windowing function -- it acts like a group by per row.
SELECT *
FROM Goods
WHERE COUNT(*) OVER (PARTITION BY Product, Price) > 1