title: Returning 0 value with COUNT(*) in postgres with multiple subcategories
body: <p>I'm having trouble getting COUNT(*)=0 to show up in columns.  This question has been addressed on some level here: <a href="https://stackoverflow.com/questions/11761171/how-to-make-this-query-also-return-rows-with-0-count-value">how to make this query also return rows with 0 count value?</a></p>
  
  <p>...but I'm having trouble generalizing the solution to more than one distinct category.  Here's my situation:  I have 11 distinct categories of parking location and 4 distinct categories of affiliation:</p>
  
  <pre>
  
  
      # SELECT DISTINCT parking_location FROM respondents;
       parking_location 
      ------------------
       on-street free
       city garage
       UC lot
       rpp visitor
       off-street free
       other
       nowhere
       other paid
       meter
       disabled
       rpp
      (11 rows)
  
      # SELECT DISTINCT affiliation FROM respondents;
       affiliation 
      -------------
       faculty
       undergrad
       grad
       staff
      (4 rows)
  
  </pre>
  
  <p>None of my undergrad respondents use disabled parking, so when I try to count them by parking_location, I only get 10 rows back:</p>
  
  <pre>
  
      SELECT parking_location,COUNT(*) FROM respondents WHERE affiliation='undergrad' GROUP BY parking_location;
       parking_location | count 
      ------------------+-------
       on-street free   |     2
       meter            |    25
       city garage      |     5
       rpp              |    21
       nowhere          |  1012
       UC lot           |    33
       rpp visitor      |    10
       off-street free  |    10
       other            |    10
       other paid       |    12
      (10 rows)
  
  </pre>
  
  <p>No problem.  The aforementioned link shows how to make the 0 appear:</p>
  
  <pre>
  
      ths=# WITH c as (SELECT DISTINCT parking_location FROM respondents),
      ths-# r AS (SELECT affiliation, parking_location, COUNT(*) AS count FROM respondents WHERE affiliation='undergrad' GROUP BY 1,2)
      ths-# SELECT c.parking_location, COALESCE(r.count, 0) AS count FROM c
      ths-# LEFT JOIN r ON c.parking_location = r.parking_location
      ths-# ORDER BY parking_location;
       parking_location | count 
      ------------------+-------
       nowhere          |  1012
       meter            |    25
       rpp              |    21
       rpp visitor      |    10
       on-street free   |     2
       UC lot           |    33
       off-street free  |    10
       city garage      |     5
       other paid       |    12
       disabled         |     0
       other            |    10
      (11 rows)
  
  </pre>
  
  <p>But now I want to show the table with all affiliations, not just undergrads.  Further, I want to order the resulting table first by affiliation and then by parking_location.  I thought I could just eliminate the WHERE clause in the above, but then my undergrad disabled column vanishes:</p>
  
  <pre>
  
      ths=#  WITH c as (SELECT DISTINCT parking_location FROM respondents), 
      ths-# r AS (SELECT affiliation, parking_location, COUNT(*) AS count FROM respondents GROUP BY affiliation,parking_location)
      ths-# SELECT r.affiliation, c.parking_location, COALESCE(r.count, 0) FROM c
      ths-# LEFT JOIN r ON c.parking_location = r.parking_location
      ths-# ORDER BY affiliation,parking_location;
       affiliation | parking_location | coalesce 
      -------------+------------------+----------
      
       staff       | city garage      |       34
       staff       | other paid       |       50
       staff       | disabled         |       18
       staff       | other            |       61
       undergrad   | nowhere          |     1012
       undergrad   | meter            |       25
       undergrad   | rpp              |       21
       undergrad   | rpp visitor      |       10
       undergrad   | on-street free   |        2
       undergrad   | UC lot           |       33
       undergrad   | off-street free  |       10
       undergrad   | city garage      |        5
       undergrad   | other paid       |       12
       undergrad   | other            |       10
       grad        | nowhere          |     1113
       grad        | meter            |       96
       grad        | rpp              |       31
      
  
  </pre>
  
  <p>Any help?</p>