MySQL select count

Question

I am trying to count the number of companies that have at least one product from the following query

SELECT count(*)
FROM company c
JOIN product p on c.id = product.company_id
WHERE p.is_deleted = 0
AND c.is_customer = 1
AND c.company_type_id = 5
GROUP by c.id

So, this shows me a list of all companies, and the count of products for each company.

What I am trying to achieve is a count of companies from the above result.

This can be achieved as follows:

SELECT count(*)
FROM ( 
    SELECT count(*)
    FROM company c
    JOIN product p on c.id = product.company_id
    WHERE p.is_deleted = 0
    AND c.is_customer = 1
    and c.company_type_id = 5
    GROUP by c.id) AS t1

So, this gives me the correct result, but I am just wondering if there is a more efficient way of doing things.

Answer 1

I believe you can simplify it to this:

SELECT count(distinct c.id)
FROM company c
JOIN product p on c.id = product.company_id
WHERE p.is_deleted = 0
AND c.is_customer = 1
AND c.company_type_id = 5