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.