Mysql counting join table OneToMany

Task:

id | title   | description | 
---------------------------------------------------------------------
 1 | Task1   | Descr1      | 
 2 | Task2   | Descr1      | 
 3 | Task2   | Descr1      | 
 4 | Task2   | Descr1      | 
 5 | Task2   | Descr1      |

Message:

id | task_id | message   | status |
---------------------------------------------------------------------
 1 | 1       | Message1  | HOLD
 2 | 1       | Message2  | OK
 3 | 1       | Message3  | ERROR
 4 | 1       | Message4  | ERROR
 5 | 2       | Message5  | HOLD
 6 | 2       | Message6  | OK
 7 | 2       | Message7  | OK
 8 | 2       | Message7  | OK
 9 | 3       | Message7  | OK

I want to show as here:

id | title   | description | count(HOLD) | count(OK) | count(ERROR)
---------------------------------------------------------------------
 1 | Task1   | Descr1      | 1           | 1         | 2
 2 | Task2   | Descr1      | 1           | 3         | 0
 3 | Task2   | Descr1      | 0           | 1         | 0
 4 | Task2   | Descr1      | 0           | 0         | 0
 5 | Task2   | Descr1      | 0           | 0         | 0

Answer

You could use a selective aggregation baded on sum and CASE when

  select  task.id
    , task.title  
    , task.description
    , sum(case when Message.status = 1 then 1 else 0 end )  status1
    , sum(case when Message.status = 2  then 1 else 0 end )  status2
    , sum(case when Message.status = 3  then 1 else 0 end )  status3
from Task
INNER JOIN Message ON Task.id = Message.task_id
group by task.id
    , task.title  
    , task.description

Advertisement

Answer