title: Conditional count when case = 1, DB2
body: <p>I'm currently trying to figure out the best way to do a conditional count as an alias in DB2 for Iseries. The below values represent job statuses where a job can be created, completed and cancelled so any one job will possibly have multiple status codes attached to it. </p>
  
  <p>However, for my final value, I'm trying to get a count of jobs that only have the created status so that I can show how many are still open jobs. Basically looking for cases where the count for the created case = 1, but the below fails at the '='</p>
  
  <pre><code>SELECT
    COUNT(CASE A1.JOB WHEN = 'CREATED' THEN 1 END) AS CREATED,
    COUNT(CASE A1.JOB WHEN = 'CANCELLED' THEN 1 END) AS CANCELLED,
    COUNT(CASE WHEN A1.JOB 'CREATED' = 1 then 1  END)  AS OPEN
  FROM SCHEMA.TABLE A1;
  </code></pre>
  
  <p>sample data and results:</p>
  
  <pre><code>    Job ID   |   Status_code
  -------------------------
  123         'CREATED'
  123         'COMPLETED'
  521         'CREATED'
  521         'CANCELLED'
  645         'CREATED'
  </code></pre>
  
  <p>Results:</p>
  
  <pre><code>JOB  |  CREATED  |   CANCELLED   |   OPEN
  -------------------------------------------
  123     1               0               0
  521     1               1               0
  645     1               0               1
  </code></pre>