title: Return a row with count of 0 when GROUP BY yields no matches
body: <p>Trying to write a query in MySQL that takes 4 different actions performed by a specific <code>deviceToken</code> and within a specific timeframe and returns the <code>action</code>, the <code>deviceToken</code>, and the <code>count</code> of how many times that action was taken. My problem is, in order to handle the result properly, I always need all 4 actions to be a part of the return, even if the count is 0, and <code>GROUP BY</code> notoriously does not play along with that. Here is the query I have currently:</p>
  
  <pre><code>SELECT `action`, 
         deviceToken, 
         CASE WHEN `action` = 'calendar' THEN COUNT(*) END AS CalCount,
         CASE WHEN `action` = 'closinginfo' THEN COUNT(*) END AS CloseingCount,
         CASE WHEN `action` = 'generalinfo' THEN COUNT(*) END AS GenInfoCount,
         CASE WHEN `action` = 'login' THEN COUNT(*) END AS LoginCount   
  FROM `action`
  WHERE deviceToken = '44262f82-3767-49c8-8786-ee377ea00a64'
      AND (`action` = 'login' OR `action` = 'calendar' OR `action` = 'closinginfo' OR `action` = 'generalinfo')
      AND `timestamp` &gt; CONCAT(DATE(NOW()),' 23:59:59') - INTERVAL DAYOFWEEK(NOW())+14 DAY
      AND `timestamp` &lt; CONCAT(DATE(NOW()),' 23:59:59') - INTERVAL DAYOFWEEK(NOW())+7 DAY
  GROUP BY `action`
  </code></pre>
  
  <p>For a <code>deviceToken</code> that has performed all 4 actions within that time frame, the result looks like this:</p>
  
  <p><a href="https://i.stack.imgur.com/yXccC.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/yXccC.png" alt="enter image description here"></a></p>
  
  <p>The way my client handles this result is already perfect. However, a <code>deviceToken</code> that has taken less than all 4 actions looks like this: </p>
  
  <p><a href="https://i.stack.imgur.com/3zoRq.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/3zoRq.png" alt="enter image description here"></a></p>
  
  <p>What I need to have for my data handling to work universally is those missing rows still returned, but just list those counts as 0. Does anyone have any idea of how to accomplish this?</p>