MySQL select count

Question

I am trying to calculate 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 number of products for each company.

What I'm trying to achieve is the number 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'm just wondering if there is a more efficient way to do something.

Answer 1

I believe you can simplify 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