I have the following table
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.
I have tried many solutions for hours with no luck. Any help would be greatly appreciated. Thank you.
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:
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:
Next time I answer a question, I’ll make sure I have a cup of coffee first 🤪