0

I'm, trying to write a query that will allow me to do the following:

"Find total number of open and closed tickets per day, within a specific date range".

Here's what I got so far

SELECT
COUNT(tikcet_id) as totalTickets, 
COUNT(CASE WHEN ticket_status = 'Complete' 
      THEN 1 
      ELSE NULL END) as closedTickets,
DATE(ticket_date)
FROM tickets 
WHERE (DATE(ticket_date) BETWEEN DATE('2014-07-18') AND DATE('2014-07-30'))
GROUP BY ticket_date

This is the results I'm getting, it counts the tickets fine, it just doesn't group them the way I want.

TotalTickets| ClosedTickets| ticket_date
           1|             0| 2014-07-21 10:00:00
           1|             0| 2014-07-21 10:21:21
           0|             1| 2014-07-21 11:45:12
           0|             1| 2014-07-22 09:21:24
           0|             1| 2014-07-22 09:43:23

I would like to have them grouped by day, so for example I should be able to see something like that:

TotalTickets| ClosedTickets| ticket_date
           2|             1| 2014-07-21
           2|             0| 2014-07-22
Charlesliam
  • 1,293
  • 3
  • 20
  • 36
Artano
  • 23
  • 2

1 Answers1

0

Make this 

GROUP BY DATE(ticket_date)

instead of

GROUP BY ticket_date

You're doing it right in the SELECT clause already.

And btw, a shorter way of writing this

COUNT(CASE WHEN ticket_status = 'Complete' 
      THEN 1 
      ELSE NULL END) as closedTickets,

is 

SUM(ticket_status = 'Complete') as closedTickets,

boolean expressions evaluate to true or false, 1 or 0.

fancyPants
  • 50,732
  • 33
  • 89
  • 96
  • Thank you very much. I have one more question, is there a way to show days that have no tickets at all? I'm currently getting results only for days which have records. – Artano Jul 30 '14 at 08:58
  • You can't select what isn't there. Either solve it on application level, or you create a table just containing dates and left join it. – fancyPants Jul 30 '14 at 09:03