title: Selecting COUNT from different criteria on a table
body: <p>I have a table named 'jobs'.  For a particular user a job can be active, archived, overdue, pending, or closed.  Right now every page request is generating 5 COUNT queries and in an attempt at optimization I'm trying to reduce this to a single query.  This is what I have so far but it is barely faster than the 5 individual queries.  Note that I've simplified the conditions for each subquery to make it easier to understand, the full query acts the same however.</p>
  
  <p>Is there a way to get these 5 counts in the same query without using the inefficient subqueries?</p>
  
  <pre><code>SELECT
    (SELECT count(*)
      FROM "jobs"
      WHERE
        jobs.creator_id = 5 AND
        jobs.status_id NOT IN (8,3,11) /* 8,3,11 being 'inactive' related statuses */
    ) AS active_count, 
    (SELECT count(*)
      FROM "jobs"
      WHERE
        jobs.creator_id = 5 AND
        jobs.due_date &lt; '2011-06-14' AND
        jobs.status_id NOT IN(8,11,5,3) /* Grabs the overdue active jobs
                                        ('5' means completed successfully) */
    ) AS overdue_count,
    (SELECT count(*)
      FROM "jobs"
      WHERE
        jobs.creator_id = 5 AND
        jobs.due_date BETWEEN '2011-06-14' AND '2011-06-15 06:00:00.000000'
    ) AS due_today_count
  </code></pre>
  
  <p>This goes on for 2 more subqueries but I think you get the idea.</p>
  
  <p>Is there an easier way to collect this data since it's basically 5 different COUNT's off of the same subset of data from the jobs table?</p>
  
  <p>The subset of data is 'creator_id = 5', after that each count is basically just 1-2 additional conditions.  Note that right now we're using Postgres but may be moving to MySQL in the near future.  So if you can provide an ANSI-compatible solution I'd be gratetful :)</p>