Merge output of two Queries MYSQL

Question

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.

Answer 1

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

Answer 2

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;