title: conditional group by in Oracle
body: <p>I'm trying to write a query to get the sum of a table with condition.
  for example this table</p>
  
  <pre><code>area    |    machine    |    status    |    qty
  
  1       |    machine1   |    inprocess |    210
  1       |    machine2   |    pending   |    120
  1       |    machine3   |    done      |    50
  2       |    machine1   |    inprocess |    30
  2       |    machine2   |    done      |    170
  </code></pre>
  
  <p>the result that I want would be</p>
  
  <pre><code>area  |  inprocess sum  |  pending sum   | done sum  |  all sum
  
  1     |  210            |  120           | 50        |  380
  2     |  30             |  0             | 170       |  200
  </code></pre>
  
  <p>I'm thinking about group by clauses with conditionals but I can't seem to get the solution that I need for this problem. What I tried was</p>
  
  <pre><code>select distinct 
      area,
      (select sum(qty) from table
       where status = 'inprocess'
       and area = mainTable.area) as inprocess sum
      (select sum(qty) from table
       where status = 'pending'
       and area = mainTable.area) as pending sum
      (select sum(qty) from table
       where status = 'done'
       and are = mainTable.area) as done sum
  from table mainTable
  </code></pre>
  
  <p>up to a point, it does work but when I try to check if I'm getting the correct quantity I can't seem to match the actual value with the result of my query. (I'm working at a much larger data set, millions of records). I guess what I'm really after is a group by clause with which I can put conditions in so it will only aggregate records that would match the condition. Please shed light jedi masters</p>