title: BD2: SQL _CASE with group by
body: <p>I have the following tables</p>
  <pre><code>---SALARY_ITEMS---
  PERSONID  | EMPLOYMENT _REF | GROUP1  | CODE |   FROM   |    END    | QUANTI
  000101        XYX              400      11101 2020-02-12  2020-02-12    12
  000101        XYX              300      1100  2020-01-29  2020-02-29    1
  000102        XYY              450      11111 2020-02-01  2020-02-12    19
  000102        XYY              400      11101 2020-02-02  2020-02-12    82
  000103        XYA              500      1100  2020-02-10  2020-02-12    11
  000104        XYB              700      1100  2020-01-12  2020-02-12    24 
  ---PERSON ---
  PERSONID    NAME 
  000101      Carolina
  000102      Helen
  000103      Jack
  000104      Anna
  ---EMPLOYMENT---
  PERSONID   EMPLOYMENT _REF    POSITION
  000101           XYX          doctor
  000102           XYY          nurse
  000103           XYA          nurse
  000104           XYB          Proffesor
  ----absent---
  PERSONID     CODE2   FROM             END
  000101        123   2020-03-01       2020-06-30
  000102        120   2020-02-05       2020-02-13
  000102        123   2020-03-01       2020-03-28
  000103        115   2020-05-05       2020-06-30
  000104        123   2020-02-01       2020-05-30
  </code></pre>
  <p>What I tried to do: get all employee that they are doctor and nurse and have certain group with certain code and works over 100 hours in a 2020 -Feb.</p>
  <p>The following SQL query give me what i want  But i want to add something to my query that is :</p>
  <p>create a new column to see if the employee was absent in the same period 2020-feb with absent code 120 or 119 or both.</p>
  <p>If he was I will get the 'CODE2'  ELSE 'NOTHING'.</p>
  <p>How can I do this in DB2?</p>
  <p>This is the result I need to get:</p>
  <pre><code>PERSONID  | NAME | POSITION | QUANTITY |ABSENT (this what i want to have)
  000102      Helen   NURSE       101       120   
         
  </code></pre>
  <p>Query:</p>
  <pre><code>SELECT
      S.PERSONID, P.NAME,E.POSTION , sum(S.QUANTITY) as QUANTITY
  FROM 
      SALARY_ITEMS  S  
  LEFT JOIN 
      PERSON P ON S.PERSONID = P.PERSONID
  LEFT JOIN  
      EMPLOYMENT E ON E.EMPLOYMENT_REF = S.EMPLOYMENT _REF
  WHERE
      S.group1 IN ('400', '440', '450', '470', '640') 
      AND S.code IN ('11101', '11111', '11121', '11131', '11141')
      AND S.from &gt;= '2020-02-01'
      AND S.end &lt;= '2020-02-29'
      AND E.POSTION IN ('nurse', 'doctor')
      AND (SELECT SUM(S2.QUANTITY) AS QUANTITY2
           FROM SALARY_ITEMS S2   
           WHERE S2.group1 IN ('400', '440', '450', '470', '640')
             AND S2.code IN ('11101', '11111', '11121', '11131', '11141')
             AND S2.from &gt;= '2020-02-01'
             AND S2.end &lt;= '2020-02-29'
             AND S.PERSONID = S2.PERSONID) &gt;= '100'
  GROUP BY  
      S.PERSONID, P.NAME, E.POSTION
  </code></pre>