Alex Bloom Alex Bloom - 1 month ago 4
MySQL Question

mysql COUNT() with CASE

I've run into a problem with how this query is returning the data. The

timestamp
returns correct, and
newpost
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
timestamp
for the chart and as the creation time for the ticket aswell as
newpost
.

At the end of the day the 18th should have 12
newposts
, 1
solved
and 22
closed
but its not working

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


SAMPLE DATA:
tickets
---

| 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 |


@drapp

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 |


Results of broken query
Heres The results

Answer

Ok, I THINK I understand what you want, and I will clarify. Hopefully my writing will help you explain future posts too.

For your data, you have tickets with when created, when closed, and a status of how closed.

I need to retrieve a summary based on EACH DAY of the activity in question. So even though a ticket may have been created on Oct 9th and closed on Oct 18, I need the closed date activity to appear on the 18th summary record.

To do this, you need a subquery to get all closed activity summarized AND the primary based on the date in question. But now, what if you have a date where no tickets are created, but some are closed... I know, anomaly condition and I am not going to query that.

So, an adjusted query as

SELECT
      DATE(T1.created) DateActivity,
      COUNT(T1.*) newpost,
      MAX( COALESCE( ClosedActivity.ClosedEntries, 0 )) as ClosedEntries,
      MAX( COALESCE( ClosedActivity.ClosedPost, 0 )) as ClosedPosts,
      MAX( COALESCE( ClosedActivity.SolvedPost, 0 )) as SolvedPosts
   FROM 
      tickets T1
         LEFT JOIN 
         ( select 
                 DATE( closed ) DateClosed,
                 COUNT(*) as ClosedEntries,
                 SUM( case when sent = 2 then 1 else 0 end ) as ClosedPost,
                 SUM( case when sent = 0 OR sent = 1 then 1 else 0 end ) as SolvedPost
              from
                 tickets
              where
                 closed >= date_add( current_date(), interval -7 day )
              GROUP BY 
                 DATE(closed) ) ClosedActivity
            ON 
               DATE( T1.Created ) = ClosedActivity.DateClosed
   where
      T1.created >= date_add( current_date(), interval -7 day )
   GROUP BY 
      DATE(T1.created)
   ORDER BY 
      DATE(T1.created) DESC
   LIMIT 7

Notice I applied date math to subtract 7 days from current. This way it only grabs activity where the ticket was created within the last 7 days. If your data has years of data, it will chew through all of it, THEN return only the most recent 7. I am starting with only the last 7 days of activity.

Since a closed ticket can never be earlier than when it was created, that to is qualified within the 7 days.

So now with the individual row per date on the inner query, I am applying a MAX() of that result since it will never change based on the number of outer entries of created tickets. Otherwise, the engine might nag about columns not part of the group by clause that are not aggregate columns.

I THINK this is what you are looking for.

Comments