Mysql query to get customer count from table

This must work. Basically what this query does is sum the cases valid for your requirements.

These cases are:

• The product is a parent
• The product is a child but there is not a buy for the parent
• Else => 0 (not sum)

Then, with this clasification, you can add the occurrences.

select d.serial_number, d.product_type, sum(counter) as customer_count
from (
  select *,
    case 
      when y.id = y.parent_prod_id then 1
      when not exists (
        select 1 
        from your_data yy 
        where y.customer_id=yy.customer_id 
          and yy.id = y.parent_prod_id
        ) then 1 
      else 0 
    end counter
  from your_data y
  ) d
group by d.serial_number, d.product_type

You can test on this <>db_fiddle

You can do it with simple join and conditional aggregation.

Schema and insert statements:

 create table yourtable(customer_id int,    id int, product_type varchar(50), serial_number int, parent_prod_id int);
 insert into yourtable values(123,200,  'Camera',   3222333,200);
 insert into yourtable values(123,201,  'InstaCam', 3322322,200);
 insert into yourtable values(123,202,  'InstaCam', 4332233,200);
 insert into yourtable values(125,200,  'Camera',   3222333,200);
 insert into yourtable values(126,200,  'Camera',   3222333,200);

Query:

    select a.serial_number, a.product_type,sum(case when a.id=b.id then 1 else 0 end)customer_count
     from yourtable a 
     left join yourtable b on a.parent_prod_id=b.id and a.customer_id=b.customer_id
     group by a.serial_number,  a.product_type

Output:

db<>fiddle here

You can use distinct on customer_id such as SELECT count(distinct customer_id) so to count a customer only once.

https://www.mysqltutorial.org/mysql-distinct.aspx

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