CODEWITHSUNDEEP
mysqlsql
stighy
stighy

Reputation: 7170

Get all customer that bought 2 product

I've the following 'simple' dataset 

Customer  product    qty

A           p1        1
A           p2        1
B           p1        1
B           p3        1
C           p1        1
D           p2        1
D           p1        1

I need to get all customer that bought BOTH p1 and p2 , so the result will be A and D. How to do in mysql without pivoting ?

Thanks

Upvotes: 1

Views: 126

Answers (2)

Strawberry
Strawberry

Reputation: 33945

I'm repeating Alma's answer because I think the commentary attached to that answer introduces unnecessary confusion which the answer itself does not adequately address.

SELECT customer
     , COUNT(DISTINCT product) ttl -- DISTINCT is only necessary if (customer,product) is NOT unique
  FROM t
 WHERE product IN ('p1', 'p2')
 GROUP 
    BY customer
HAVING COUNT(*) = 2 -- where 2 is equal to the number of items in IN();

Upvotes: 2

Alma Do
Alma Do

Reputation: 37365

If you want exact match (i.e. strictly p1, p2 and nothing else), then:

SELECT
  Customer,
  COUNT(DISTINCT product) AS products_count
FROM 
  t
WHERE
  product IN ('p1', 'p2')
GROUP BY
  Customer
HAVING
  -- >=2 in case if you'll want to include other products, not only p1 and p2
  -- but p1 and p2 must present. Note, that IN() must be modified also
  products_count=2

upd. (This gone after editing) with "at least" you'll need to add some extra-conditions to IN (or values into it) - that's because current WHERE condition will leave only p1 and p2 .

Upvotes: 1

Related Questions