title: Oracle SQL help counting the same thing two different ways, but with common grouping
body: <p>I have a table that contains for each entry an <code>incident_id</code>, <code>status</code> (Let's say either <code>open</code> or <code>closed</code>, <code>date_raised</code> (Date) and <code>closure_date</code> (Date).</p>
  
  <p>I want to show a table that counts the number of incidents closed on a closure date (so the count of <code>incident_id</code> where <code>status='closed' and closure_date is not null</code>), and the number of incidents that remain open (count of <code>incident_id</code> where <code>status='open'</code> on that same day.</p>
  
  <p>In case I've confused you, a table that looks like this:</p>
  
  <pre><code> ______________________________________________________________________________
  | closure date | count of incidents closed | count of incidents remaining open |
  |--------------|---------------------------|-----------------------------------|
  | 01-Sep-12    | 5                         | 14                                |
  | ...          | ...                       | ...                               |
  </code></pre>
  
  <p>I've managed a table that does the count of incidents closed like this:</p>
  
  <pre><code>SELECT COUNT(incident_id)
  WHERE closure_date IS NOT NULL AND status="open"
  GROUP BY closure_date
  </code></pre>
  
  <p>I've tried for hours now to get the other count working, but can't so far :-(</p>
  
  <p>Edit: Here is an example of the table I have:</p>
  
  <pre><code> ___________________________________________________
  | incident_id | status | date_raised | closure_date |
  |-------------|--------|-------------|--------------|
  | 1           | closed | 01-Sep-12   | 01-Sep-12    |
  | 2           | open   | 30-Aug-12   | (null)       |
  | 3           | open   | 02-Sep-12   | (null)       |
  | 4           | closed | 02-Sep-12   | 05-Sep-12    |
  | ...         | ...    | ...         | ...          |
  </code></pre>
  
  <p>Would give the table:</p>
  
  <pre><code> ______________________________________________________________________________
  | closure date | count of incidents closed | count of incidents remaining open |
  |--------------|---------------------------|-----------------------------------|
  | 01-Sep-12    | 1                         | 1                                 |
  | 05-Sep-12    | 1                         | 2                                 |
  </code></pre>