SQL COUNT with WHERE clause

Question

I have a seemly simple question with SQL that I am having trouble figuring out. Suppose I have the following tables:

company: 
    company_id
    company_title

users: 
    user_id
    username

company_owners: 
    company_id
    user_id

Now, there can be multiple users as company owners. Here is some example data:

company:
1, "A Company"
2, "B Company"
3, "C Company"

users:
1, "A User"
2, "B User"
3, "C User"

company_owners:
1,1
1,2
1,3
2,3

I am trying to create a query (MySQL) that gets the company_title, as well as the number of owners for that company, based on a specific company owner. So, for example:

Example query (in english) run: Get number of owners for each company that "C User" is an owner for:

company_id=1, company_title="A Company", num_owners=3
company_id=2, company_title="B Company", num_owners=1
company_id=3, company_title="C Company", num_owners=0

I have tried:

SELECT COUNT(user_id), company.* FROM `company` 
LEFT JOIN `company_owners` ON company_owners.company_id = company.company_id 
WHERE company_owners.user_id=1 GROUP BY company_id

But that always gives me an ownership number of "1", I'm assuming because its only COUNTing the rows where user_id=1.

Does anyone have any ideas? I can supply more detail if need be.

Thanks so much!

Solution 1

Your WHERE clause should be "where there exists an owner for this company with user_id = 1".

SELECT COUNT(user_id), company.*
FROM `company` 
LEFT JOIN `company_owners`
ON company_owners.company_id = company.company_id 
WHERE EXISTS
(
    SELECT *
    FROM company_owners AS co2
    WHERE company_owners.company_id = co2.company_id
    AND co2.user_id = 3
)
GROUP BY company_id

See it working online: sqlfiddle

Solution 2

select company.company_id, company_title, count(*)
from company join company_owners on company.company_id = company_owners.company_id
where exists (select 1 from company_owners co where co.user_id = 1 and co.company_id = company.company_id)
group by company.company_id, company_title