title: MySQL Select Case - Comparison Between Two Tables
body: <p>I have two tables similar to these:</p>
  
  <pre><code>Table: case
  
  ---------------------------------------------------
  | id |  company | managed |    time    |  client  |
  ---------------------------------------------------
  | 1  |  apple   |   yes   | 1412643785 |    no    |
  ---------------------------------------------------
  | 2  |  barilla |   no    | 1412643785 |    no    |
  ---------------------------------------------------
  | 3  |  google  |   no    | 1412643785 |   yes    |
  ---------------------------------------------------
  | 4  |  google  |   yes   | 1412643785 |   yes    |
  ---------------------------------------------------
  | 5  |  google  |   no    | 1412643785 |   yes    |
  ---------------------------------------------------
  
  
  Table: language
  
  ---------------------------
  | id | company  |  lang   |
  ---------------------------
  | 1  |  apple   |   EN    |
  ---------------------------
  | 2  |  barilla |   IT    |
  ---------------------------
  | 3  |  google  |   EN    |
  ---------------------------
  </code></pre>
  
  <p>I have create statistics/graphics from this tablet, the I extract the following information for each month:
  * Number of cases of clients per month
  * Number of cases managed per month
  * Total number of cases per month
  * Number of cases of Italian companies per month</p>
  
  <p>For the first three points I don't have no problems, and I have made this query:</p>
  
  <pre><code>SELECT FROM_UNIXTIME(time, '%Y-%M') as 'Month', 
      COUNT(CASE WHEN client = 'yes' THEN 1 ELSE NULL END) as 'Reports of a customer,
      COUNT(CASE WHEN managed = 'yes' THEN 1 ELSE NULL END) as 'Managed cases',
      COUNT(id) as 'Total reports'
  FROM case
  GROUP BY FROM_UNIXTIME(time, '%Y-%M')
  ORDER BY FROM_UNIXTIME(time, '%Y-%m')
  </code></pre>
  
  <p><strong>But how do I extract the monthly number of cases managed of any Italian company?</strong></p>
  
  <p>I tried to add this portion of query but does not go...</p>
  
  <pre><code>COUNT(CASE WHEN case.company = language.company AND language.lang = 'IT'  THEN 1 ELSE NULL END) as 'Italian Case',
  </code></pre>
  
  <p>Can you help me? Thank you</p>