GROUP BY multiple tables MYSQL

Question

I have three tables which I want to query in MySQL as follows:

Table employee

id  | name_employee
-------------------
1   | John
2   | David
3   | Sara
4   | Tina

Table Status

id  | name_status
-----------
1   | no process
2   | processing
3   | processed

Table Ticket

id  |  id_employee  |  id_status
----------------------------------
1   |        1      |     2
2   |        2      |     2
3   |        2      |     3
4   |        3      |     3
5   |        1      |     1

I need to query the result as follows:

name_employee  |  count(ticket.id) where id_status = 1 | count(ticket.id) where id_status = 2 | count(ticket.id) where id_status = 3
-----------------------------------------------------------------------------------------------------------------------------------------
David          |          0                            |        1                             |     1   
John           |          1                            |        1                             |     0
Sara           |          0                            |        0                             |     1

Any help much appreciated! Thanks very much!

And, as always, consider handling issues of data display in application code instead — Strawberry, Apr 05 '19 at 10:16

score 1 · Accepted Answer · answered Apr 05 '19 at 09:16

use can use case when expression

select name_employee,
count(case when id_status=1 then 1 end) as status1,
count(case when id_status=2 then 1 end) as status2,
count(case when id_status=3 then 1 end) as status3
from ticket_Table a
inner join employee b on a.id_employee=b.id
group by name_employee

GROUP BY multiple tables MYSQL

1 Answers1