Mysql query to get customer count from table

I have the following table

customer_id id product_type serial_number parent_prod_id
123 200 Camera 3222333 200
123 201 InstaCam 3322322 200
123 202 InstaCam 4332233 200
125 200 Camera 3222333 200
126 200 Camera 3222333 200

My query should return the customer count for each product type but if the same customer purchased a product such as InstaCam which is tied to the parent prod id Camera, then the customer count for the product InstaCam must be 0. In the above table, Camera was purchased by three different customers with customer ids 123, 125 and 126. Since InstaCam was also purchased by one of the customers who purchased the Camera and because the parent_prod_id of InstaCam is the same as the id of Camera, the same customer should not be counted again for the Instacam product so the customer count would be 0.

Expected output:

serial_number product_type customer_count
3222333 Camera 3
3322322 InstaCam 0
4332233 InstaCam 0

I have tried many solutions for hours with no luck. Any help would be greatly appreciated. Thank you.

Answer

To solve this, you will need to join the table to itself and compare sales.

First let’s make the table and populate it with the supplied data:

DROP TABLE IF EXISTS `Sales`;
CREATE TABLE IF NOT EXISTS `Sales` (
    `customer_id`       int(11)        UNSIGNED NOT NULL    ,
    `id`                int(11)        UNSIGNED NOT NULL    ,
    `product_type`      varchar(80)             NOT NULL    DEFAULT '',
    `serial_number`     varchar(40)             NOT NULL    DEFAULT '',
    `parent_prod_id`    int(11)        UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `Sales` (`customer_id`, `id`, `product_type`, `serial_number`, `parent_prod_id`)
VALUES (123, 200, 'Camera', '3222333', 200),
       (123, 201, 'InstaCam', '3322322', 200),
       (123, 202, 'InstaCam', '4332233', 200),
       (125, 200, 'Camera', '3222333', 200),
       (126, 200, 'Camera', '3222333', 200);

To get the results you seek, we can use a query like this:

SELECT s.`serial_number`, s.`product_type`,
       COUNT(DISTINCT CASE WHEN pp.`id` IS NOT NULL THEN NULL ELSE s.`customer_id` END) as `customer_count`
  FROM `Sales` s LEFT OUTER JOIN `Sales` pp ON s.`customer_id` = pp.`customer_id` 
                                           AND s.`parent_prod_id` = pp.`id`
                                           AND s.`id` <> pp.`id`
 GROUP BY s.`serial_number`, s.`product_type`;

This will give you a result like this:

serial_number product_type customer_count
3222333 Camera 3
3322322 InstaCam 0
4332233 InstaCam 0

Now to test this, let’s add a record for a customer who bought only an InstaCam:

INSERT INTO `Sales` (`customer_id`, `id`, `product_type`, `serial_number`, `parent_prod_id`)
VALUES (131, 201, 'InstaCam', '3322322', 200);

Run the same query as before, and you’ll get this:

serial_number product_type customer_count
3222333 Camera 3
3322322 InstaCam 1
4332233 InstaCam 0

Next time I answer a question, I’ll make sure I have a cup of coffee first 🤪