title: How can I show the counts of distinct values and include zeros?
body: <p>I have a simple MySQL DB with the following fields:</p>
  
  <pre><code>mysql&gt; SELECT * from table;
  +----+-----------+------+
  | id | location  | name | 
  +----+-----------+------+
  |  1 | NJ        | Gary |
  |  2 | MN        | Paul |
  |  3 | AZ        |      |
  |  4 | MI        | Adam |
  |  5 | NJ        |      |
  |  6 | MN        | Dave |
  +----+-----------+------+
  6 rows in set (0.00 sec)
  </code></pre>
  
  <p>I need to retrieve a list of how many people are from each state, excluding those who don't have a name. In other words, I'm trying to reproduce the following result:</p>
  
  <pre><code>+----------+-------+
  | location | count | 
  +----------+-------+
  | AZ       |     0 |
  | MI       |     1 |
  | MN       |     2 |
  | NJ       |     1 |
  +----------+-------+
  </code></pre>
  
  <p>I'm able to get close with</p>
  
  <pre><code>SELECT location, COUNT(*) AS count FROM table WHERE name!='' GROUP BY location;
  </code></pre>
  
  <p>However, COUNT(*) excludes the zero counts. I attempted to use JOIN along with the table produced by</p>
  
  <pre><code>SELECT DISTINCT location, null as count from table;
  </code></pre>
  
  <p>but a LEFT JOIN throws out the count column from the right table, and a RIGHT JOIN doesn't seem to include the zero rows or the actual counts for some reason.</p>
  
  <p>I feel as though there's a MySQL command or something simple that I'm missing. I just need to find a way to merge the two tables based on location.</p>
  
  <p>Can anybody point me in the right direction?</p>