title: mysql COUNT() with CASE
body: <p>I've run into a problem with how this query is returning the data. The <code>timestamp</code> returns correct, and <code>newpost</code> returns correct, but solved post and closed post are not coming back correct. Problem i'm running into, I had an instance today where someone closed a bunch of tickets today, but because they were older than whats being found in the past 7 days (doing this for charting) it didn't count them because i'm using created as the order by group by. The thing is created is the date it was created being used as a <code>timestamp</code> for the chart and as the creation time for the ticket aswell as <code>newpost</code>.</p>
  
  <p>At the end of the day the 18th should have 12 <code>newposts</code>, 1 <code>solved</code> and 22 <code>closed</code> but its not working</p>
  
  <pre><code>SELECT
    DATE(created) timestamp,
    COUNT(created) newpost,
    COUNT(CASE WHEN DATE(closed) AND (sent = 0 OR sent = 1) THEN closed END) solvedpost,
    COUNT(CASE WHEN DATE(closed) AND (sent = 2) THEN closed END) closedpost
  FROM 
    tickets
  GROUP BY DATE(created)
  ORDER BY DATE(created) DESC
  LIMIT 7
  </code></pre>
  
  <p>SAMPLE DATA:
  tickets
      ---</p>
  
  <pre><code>| post_id| sent |     created      |        closed  
  | 546nby | 2 | 2016-09-23 19:58:46 | 2016-10-18 19:00:53 | 
  | 540r72 | 2 | 2016-09-22 19:33:07 | 2016-10-18 18:00:56 | 
  | 54jjc2 | 2 | 2016-09-26 07:41:45 | 2016-10-18 18:00:54 | 
  | 5720p8 | 2 | 2016-10-12 02:55:35 | 2016-10-18 18:00:52 | 
  | 57a5qo | 2 | 2016-10-13 13:09:54 | 2016-10-18 18:00:50 | 
  | 4zp83k | 2 | 2016-08-26 15:40:55 | 2016-10-18 14:00:47 | 
  | 4zpu3c | 2 | 2016-08-26 17:36:19 | 2016-10-18 14:00:45 | 
  | 545qvv | 2 | 2016-09-23 17:03:08 | 2016-10-18 13:01:17 | 
  | 5475fv | 2 | 2016-09-23 21:41:17 | 2016-10-18 13:01:15 | 
  | 549u2z | 2 | 2016-09-24 12:01:35 | 2016-10-18 13:01:13 | 
  | 54ct4l | 2 | 2016-09-25 00:07:39 | 2016-10-18 13:01:11 | 
  | 54gksl | 2 | 2016-09-25 19:07:58 | 2016-10-18 13:01:09 | 
  | 54ippx | 2 | 2016-09-26 03:15:33 | 2016-10-18 13:01:07 | 
  | 54mxvf | 2 | 2016-09-26 21:09:21 | 2016-10-18 13:01:05 | 
  | 54yhfh | 2 | 2016-09-28 20:35:32 | 2016-10-18 13:01:03 | 
  | 55gxw7 | 2 | 2016-10-02 04:58:23 | 2016-10-18 13:01:01 | 
  | 55l2sz | 2 | 2016-10-03 00:11:01 | 2016-10-18 13:00:59 | 
  | 55ozq6 | 2 | 2016-10-03 18:04:24 | 2016-10-18 13:00:57 | 
  | 55txmx | 2 | 2016-10-04 15:09:11 | 2016-10-18 13:00:55 | 
  | 55vi46 | 2 | 2016-10-04 20:06:39 | 2016-10-18 13:00:53 | 
  | 55yw80 | 2 | 2016-10-05 10:56:29 | 2016-10-18 13:00:51 | 
  | 561wzj | 2 | 2016-10-05 21:26:43 | 2016-10-18 13:00:49 | 
  | 56fzlm | 0 | 2016-10-08 07:25:22 | 2016-10-18 13:00:45 | 
  | 56sjln | 1 | 2016-10-10 15:34:01 | 2016-10-18 01:00:48 | 
  | 57luzw | 1 | 2016-10-15 12:34:50 | 2016-10-17 18:00:44 | 
  | 56tlp8 | 1 | 2016-10-10 18:44:46 | 2016-10-17 16:00:44 | 
  | 578e6y | 0 | 2016-10-13 03:56:29 | 2016-10-17 05:00:47 | 
  | 56931q | 1 | 2016-10-07 02:01:46 | 2016-10-17 04:00:44 | 
  | 55wjpf | 1 | 2016-10-04 23:38:30 | 2016-10-16 23:00:47 | 
  | 56tye6 | 1 | 2016-10-10 19:45:46 | 2016-10-16 23:00:45 | 
  | 56wvge | 1 | 2016-10-11 07:07:50 | 2016-10-16 23:00:43 | 
  | 57sfd6 | 2 | 2016-10-16 17:16:28 | 2016-10-16 18:00:36 |
  </code></pre>
  
  <p>@drapp</p>
  
  <pre><code>Results
  ---
  | timestamp | newpost | solvedpost | closedpost |  
  | 2016-10-19 | 4 | 0 | 0 | 
  | 2016-10-18 | 12 | 0 | 0 | 
  | 2016-10-17 | 8 | 0 | 0 | 
  | 2016-10-16 | 6 | 0 | 1 | 
  | 2016-10-15 | 8 | 1 | 0 | 
  | 2016-10-14 | 11 | 2 | 1 | 
  | 2016-10-13 | 12 | 3 | 1 | 
  </code></pre>
  
  <p>Results of broken query
  <a href="https://i.stack.imgur.com/bOmFz.png" rel="nofollow"><img src="https://i.stack.imgur.com/bOmFz.png" alt="Heres The results"></a></p>