title: SQL COUNT with WHERE clause
body: <p>I have a seemly simple question with SQL that I am having trouble figuring out.  Suppose I have the following tables:</p>
  
  <pre><code>company: 
      company_id
      company_title
  
  users: 
      user_id
      username
  
  company_owners: 
      company_id
      user_id
  </code></pre>
  
  <p>Now, there can be multiple users as company owners.  Here is some example data:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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:</p>
  
  <p>Example query (in english) run:  Get number of owners for each company that "C User" is an owner for:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I have tried:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>But that always gives me an ownership number of "1", I'm assuming because its only COUNTing the rows where user_id=1.</p>
  
  <p>Does anyone have any ideas?  I can supply more detail if need be.</p>
  
  <p>Thanks so much!</p>