CODEWITHSUNDEEP
sqlselect
SteveU
SteveU

Reputation: 111

SQL - SELECT Statement

I have a simple SQL command that i need help with.

SELECT "01 Tickets within SLA in January: " as "Category",count(tickets.id) as "Total"     FROM tickets
  WHERE (created_at>=date('2013-01-01 00:00:00')) AND (created_at<=date('2013-01-31     23:59:00')) AND (closed_at<due_at) 
union
 SELECT "02 Tickets within SLA in February: " as "Category",count(tickets.id) as "Total"     from tickets
  WHERE (created_at>=date('2013-02-01 00:00:00')) AND (created_at<=date('2013-02-31     23:59:00')) AND (closed_at<due_at)
union
  SELECT "03 Tickets within SLA in March: " as "Category",count(tickets.id) as "Total"     from tickets
  WHERE (created_at>=date('2013-03-01 00:00:00')) AND (created_at<=date('2013-03-31     23:59:00')) AND (closed_at<due_at)
union
  SELECT "04 Tickets within SLA in April: " as "Category",count(tickets.id) as "Total"     from tickets
  WHERE (created_at>=date('2013-04-01 00:00:00')) AND (created_at<=date('2013-04-31     23:59:00')) AND (closed_at<due_at)
union
  SELECT "05 Tickets within SLA in May: " as "Category",count(tickets.id) as "Total" from     tickets
  WHERE (created_at>=date('2013-05-01 00:00:00')) AND (created_at<=date('2013-05-31     23:59:00')) AND (closed_at<due_at)
union
 SELECT "06 Tickets within SLA in June: " as "Category",count(tickets.id) as "Total" from     tickets
  WHERE (created_at>=date('2013-06-01 00:00:00')) AND (created_at<=date('2013-06-31     23:59:00')) AND (closed_at<due_at)
union
  SELECT "07 Tickets within SLA in July: " as "Category",count(tickets.id) as "Total" from     tickets
  WHERE (created_at>=date('2013-07-01 00:00:00')) AND (created_at<=date('2013-07-31    23:59:00')) AND (closed_at<due_at) 

 ORDER by Category

This query returns a value for each one, which is fine. However i want to return count(tickets.id) before i apply the "AND (closed_at

So it would return something like this.

Category                          Total  Total Tickets
01 Tickets within SLA in January:   131     400
02 Tickets within SLA in February:  132     500
03 Tickets within SLA in March:      87     100
04 Tickets within SLA in April:     121      99

Hope this makes sense.

Thanks

Upvotes: 0

Views: 333

Answers (3)

Richard
Richard

Reputation: 30618

Don't try and format your results in the SQL - this should be done in your presentation layer. You can make the query MUCH simpler by simply grouping by the year and month of the date, then using this information to make your results:

SELECT YEAR(date), MONTH(date), count(tickets.id), SUM(CASE WHEN closed_at < due_at THEN 1 ELSE 0 END) as matching
from     tickets
GROUP BY YEAR(date), MONTH(date)
ORDER BY category

Upvotes: 3

RichardTheKiwi
RichardTheKiwi

Reputation: 107706

SELECT CONCAT(MONTH(created_at), ' Tickets within SLA in ', MONTHNAME(created_at)) `Category`
      ,SUM(closed_at<due_at) `Total`
      ,count(tickets.id) `Total Tickets`
  FROM tickets
 where created_at>=date('2013-01-01') AND created_at<date('2013-08-01')
 GROUP BY MONTH(created_at), MONTHNAME(created_at)
 ORDER by `Category`;

Note: For the sake of precision and sanity, it is infinitely better to use the form

WHERE ADATE >= {start} AND ADATE < {next-start}

which is absolutely unambiguous in a mathematical sense and leaves no nanoseconds prior to {next-start} left out.

Upvotes: 0

Barmar
Barmar

Reputation: 780851

Here's how you count tickets matching a more specific condition while also counting total tickets:

 SELECT "01 Tickets within SLA in January: " as "Category",
        SUM(closed_at<due_at) as "Total",
        COUNT(tickets.id) as "Total Tickets"
 FROM tickets
 WHERE created_at BETWEEN '2013-01-01 00:00:00' AND '2013-01-31 23:59:00'

You can repeat this for each month, or use grouping as suggested elsewhere.

Upvotes: 0

Related Questions