By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE baskets (
basketNum int,
fruit varchar(20),
amount int
);
INSERT INTO baskets VALUES
(1, 'Apple', 3),
(2, 'Apple', 1),
(3, 'Apple', 2),
(3, 'Orange', 1),
(4, 'Orange', 3);
Records: 5 Duplicates: 0 Warnings: 0
SELECT * FROM baskets
WHERE basketNum IN (
SELECT basketNum
FROM baskets
GROUP BY basketNum
HAVING COUNT(DISTINCT fruit) = 2
);
| basketNum | fruit | amount |
|---|---|---|
| 3 | Apple | 2 |
| 3 | Orange | 1 |
SELECT basketNum,
SUM(amount * (fruit = 'Orange')) AS orangeCount,
SUM(amount * (fruit = 'Apple')) AS appleCount
FROM baskets
GROUP BY basketNum
HAVING COUNT(DISTINCT fruit) = 2;
| basketNum | orangeCount | appleCount |
|---|---|---|
| 3 | 1 | 2 |