1

I have a mysql table with the following schema:

+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| date       | datetime    | YES  |     | NULL    |       |
| type       | varchar(15) | YES  |     | NULL    |       |
| department | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

And the following sample data:

+---------------------+--------+------------+
| date                | type   | department |
+---------------------+--------+------------+
| 2014-07-26 23:59:09 | staff  | Support    |
| 2014-07-26 23:58:44 | staff  | Support    |
| 2014-07-26 23:56:08 | client | Support    |
| 2014-07-26 23:55:16 | staff  | Support    |
| 2014-07-26 23:54:07 | client | Support    |
| 2014-07-26 23:52:36 | staff  | Support    |
| 2014-07-26 23:51:09 | staff  | Support    |
| 2014-07-26 23:48:08 | client | Support    |
| 2014-07-26 23:46:25 | staff  | Support    |
| 2014-07-26 23:46:10 | staff  | Support    |
+---------------------+--------+------------+

I want to get the hourly count of staff and client replies in the support department by hour sorted by date.

I am able to do this using two separate queries:

mysql> Select DATE_FORMAT(date,'%m-%d %H:00') AS Date,  COUNT(*) AS 'Staff Count' from ticket_data  where type='staff' and department='support'  group by DAY(Date), HOUR(Date) ORDER BY  `Date` ASC limit 10;

+-------------+-------------+
| Date        | Staff Count |
+-------------+-------------+
| 07-20 01:00 |           4 |
| 07-20 02:00 |           3 |
| 07-20 03:00 |           4 |
| 07-20 04:00 |           5 |
| 07-20 05:00 |           6 |
| 07-20 06:00 |           2 |
| 07-20 07:00 |           7 |
| 07-20 08:00 |           6 |
| 07-20 09:00 |          10 |
| 07-20 10:00 |          12 |
+-------------+-------------+

And

Select DATE_FORMAT(date,'%m-%d %H:00') AS Date,  COUNT(*) AS 'Client Count' from ticket_data  where type='client' and department='support'  group by DAY(Date), HOUR(Date) ORDER BY  `Date` ASC limit 10;

+-------------+--------------+
| Date        | Client Count |
+-------------+--------------+
| 07-20 00:00 |            2 |
| 07-20 01:00 |            2 |
| 07-20 02:00 |            2 |
| 07-20 03:00 |            5 |
| 07-20 04:00 |            7 |
| 07-20 05:00 |            4 |
| 07-20 06:00 |            4 |
| 07-20 07:00 |            6 |
| 07-20 08:00 |            8 |
| 07-20 09:00 |           12 |
+-------------+--------------+

The output I am looking for is this:

+-------------+--------------+--------------+
| Date        | Client Count |  Staff Count |
+-------------+--------------+--------------+
| 07-20 00:00 |            2 |            2 |
| 07-20 01:00 |            2 |            2 |
| 07-20 02:00 |            2 |            2 |
| 07-20 03:00 |            2 |            2 |
| 07-20 04:00 |            7 |            2 |
| 07-20 05:00 |            4 |            2 |
| 07-20 06:00 |            4 |            2 |
| 07-20 07:00 |            6 |            8 |
| 07-20 08:00 |            8 |            8 |
| 07-20 09:00 |           12 |           12 |
+-------------+--------------+--------------+

I tried using an UNION and a JOIN but didn't have any success obviously. I would appreciate if someone could point me in the right direction or help me with this.

0

you can also do this by using case statement.

Select DATE_FORMAT(date,'%m-%d %H:00') AS Date,
sum(case when type = 'staff' then 1 else 0 end) as 'Staff Count',
sum(case when type = 'client' then 1 else 0 end) as 'Client Count'
from ticket_data  where type='staff' or type='client' and department='support' 
group by  DAY(Date), HOUR(Date) ORDER BY  `Date` ASC limit 10;

this is an example for this kind of solution http://sqlfiddle.com/#!2/8dcda/1

0

This is a common trick of counting conditionally into several columns, depending on a condition using the IF() expression:

mysql> SELECT DATE_FORMAT(date,'%m-%d %H:00') AS Date,  
              SUM(IF(type = 'client', 1, 0)) AS `Client Count`,
              SUM(IF(type = 'staff', 1, 0)) AS `Staff Count`,
FROM ticket_data  
WHERE department = 'support' 
GROUP BY DAY(`Date`), HOUR(`Date`) 
ORDER BY `Date` ASC 
LIMIT 10;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.