title: Best query for getting success/failure ratio from warehouse fact table
body: <p>I'm trying to fine tune a query and would like some feedback. I have a <code>job_fact</code> warehouse table with an unit of measure for the job's final event (<code>final_event_type</code>). I'm trying to run a query on the fact that will give me a success/failure ratio. Here's what I have so far:</p>
  
  <pre><code>SELECT
    CASE WHEN jf.final_event_type IN (4,6,8,9) THEN count(final_event_type) END as num_failures,
    CASE WHEN jf.final_event_type IN (5,7,10) THEN count(final_event_type) END as num_successes
  FROM job_fact jf
  GROUP BY jf.final_event_type;
  </code></pre>
  
  <p>This query only gives me the raw succes and failure values in a two-row result:</p>
  
  <pre><code>+----------------------+-----------------------+
  | num_failures         | num_successes         |
  +----------------------+-----------------------+
  | [NULL]               | 6                     |
  | 14                   | [NULL]                |
  +----------------------+-----------------------+
  </code></pre>
  
  <p>Does anyone know if there's a way to a) get the results on one line, and b) be able to calculate the ratio between the two (e.g. the failure percentage). I'm assuming someone will tell me that I'm better off writing a procedure for this, but I'd like to avoid it if possible. I know there's an elegant way to do this, but my sql-foo is lacking today I guess.</p>
  
  <p>I'm running PostgreSQL 9.0.1. Thanks for any assistance you may offer.</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Based off of the chosen answer (from @Ronnis), here is my final query, in case you were wondering:</p>
  
  <pre><code>select
    sum(case when final_event_type in(4,6,8,9) then 1 else 0 end) as failures,
    sum(case when final_event_type in(5,7,10)  then 1 else 0 end) as successes,
  count(final_event_type) as total_events,
    sum(case when final_event_type in(4,6,8,9) then 1 else 0 end) / count(final_event_type)::decimal as failure_percentage,
    sum(case when final_event_type in(5,7,10) then 1 else 0 end) / count(final_event_type)::decimal as success_percentage
  from job_fact;
  </code></pre>