Showing customers who have purchased more than 1 product, but from the same subscription

I’m trying to find the number of customers who have ordered more than one product, with the same subscription.

I’ve first selected the count of the id_customer from customer. Then joined on subscription and order (on the correct keys). This was done so that I have all the data available to me from all 3 tables. Then grouped by the id_customer to get just the unique customers. And lastly filtered to have a fk_product (products a customer has) greater than 1.

SELECT COUNT(t1.id_customer)
FROM customer t1
INNER JOIN subscription t2 ON t1.id_customer = t2.fk_customer
INNER JOIN order t3 ON t2.id_subscription = t3.fk_subscription
GROUP BY t1.id_customer
HAVING COUNT(t3.fk_product) > 1

I’d like to better understand if this is the correct syntax to obtain the data I’m looking for. Since I have t2.id_subscription and t3.fk_subscription linked, wouldn’t this be correct? I’m still getting the wrong output. I’m thinking its perhaps the way I have my scopes, or some subtle aspect of SQL that I’m not using/understanding.

Thank you for your help!!

Answer

Use two levels of aggregation. Your data model is a bit hard to follow, but I think:

SELECT COUNT(DISTINCT so.fk_customer)
FROM (SELECT s.fk_customer, s.id_subscription
      FROM subscription s
           order o
           ON s.id_subscription = o.fk_subscription
      GROUP BY s.fk_customer, s.id_subscription
      HAVING MIN(o.fk_product) <> MAX(o.fk_product)
     ) so

Advertisement

Answer