title: SQL: Multiple count statements with different criteria
body: <p>I was just wondering if there is any way to get two separate "count" totals from a table using one query? That is, using a table similar to the following I would like to retrieve each code (distinct) and show the total number of status' NOT equal to X or D, and then have an additional column that shows the total number of status' equal to X or D and the cancel date is greater than a given date (say, the last 14 days).</p>
  
  <p>Table:</p>
  
  <pre><code>Code:  Status  Cancel_Date
  -----------------------------------
  AAA    X       2012-02-01
  AAA
  BBB    X       2012-02-01
  AAA    D       2012-01-01
  AAA
  BBB    
  BBB    D       2012-02-01
  BBB    X       2012-01-01
  </code></pre>
  
  <p>Example result (based on the above data):</p>
  
  <pre><code>Code:  TotalNotXorD     TotalXorD
  ------------------------------------
  AAA    2                1
  BBB    1                2
  </code></pre>
  
  <p>TotalNotXorD: e.g. </p>
  
  <pre><code>select code, count(*) 
  from table 
  where status not in('X','D') 
  group by code
  </code></pre>
  
  <p>TotalXorD: e.g. </p>
  
  <pre><code>select code, count(*) 
  from table 
  where status in('X','D') 
    and cancel_date &gt;= '2012-02-01' 
  group by code
  </code></pre>
  
  <p>I have looked at doing subqueries etc. but I can't seem to get the results I need.</p>
  
  <p>Any ideas?</p>
  
  <p>Thanks.</p>