title: Oracle - counting the result of a CASE statement
body: <h2>The Goal / Background</h2>
  
  <ul>
  <li>I am combing through a workorder system to see if workorders meet certain criteria 
  <ul>
  <li>(e.g. if they're not stage 3, stage 2, or even stage 1). </li>
  </ul></li>
  <li>These "stages" have been defined by management. </li>
  <li>I would like to group by the year reported, then the craft, and then see how many work orders for that grouping are in each of those 3 "not stages".</li>
  </ul>
  
  <h2>The Query</h2>
  
  <pre><code>select yearreported
  , theleadcraft
  , count(NotStage3)
  , count(NotStage2)
  , count(NotStage1)
  
  from
  (
      select extract(year from reportdate) as YearReported
      , Nvl(leadcraft, 'NONE') as TheLeadCraft
      , CASE when status not in ('CAN', 'CLOSE') then 1 else 0 END as NotStage3
      , CASE when status not in ('CAN', 'CLOSE', 'COMP') then 1 else 0 END as NotStage2
      , CASE when status not in ('CAN', 'CLOSE', 'COMP', 'WORKDONE') then 1 else 0  END as NotStage1
      from workorder
  ) query 
  
  group by yearreported, theleadcraft;
  ;
  </code></pre>
  
  <h2>The Problem / Question</h2>
  
  <ul>
  <li>This appears to work, but all counts for notstage1, notstage2, and notstage1 come out the same, despite querying for some situations and finding some that I know to be different.</li>
  <li>Is this the correct way to implement a case statement that I want to count? </li>
  <li>Should I be using DECODE() instead?</li>
  </ul>
  
  <p>Thanks in advance for any help!</p>