How to combine these two queries into one? (multiple joins against the same table)

Question

Given two tables, one for workers and one for tasks completed by workers,

CREATE TABLE IF NOT EXISTS `workers` (
  `id` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
);

INSERT INTO `workers` (`id`) VALUES
(1);


CREATE TABLE IF NOT EXISTS `tasks` (
  `id` int(11) NOT NULL,
  `worker_id` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
);

INSERT INTO `tasks` (`id`, `worker_id`, `status`) VALUES
(1, 1, 1),
(2, 1, 1),
(3, 1, 2),
(4, 1, 2),
(5, 1, 2);

I'm trying to get the number of tasks each worker has with each status code.

I can say either

SELECT w.*
,COUNT(t1.worker_id) as status_1_count
FROM workers w
LEFT JOIN tasks t1 ON w.id = t1.worker_id AND t1.status = 1 
WHERE 1 
GROUP BY 
t1.worker_id
ORDER BY w.id

or

SELECT w.*
,COUNT(t2.worker_id) as status_2_count
FROM workers w
LEFT JOIN tasks t2 ON w.id = t2.worker_id AND t2.status = 2
WHERE 1 
GROUP BY 
t2.worker_id
ORDER BY w.id

and get the number of tasks with a single given status code, but when I try to get the counts for multiple task statuses in a single query, it doesn't work!

SELECT w.*
,COUNT(t1.worker_id) as status_1_count
,COUNT(t2.worker_id) as status_2_count
FROM workers w
LEFT JOIN tasks t1 ON w.id = t1.worker_id AND t1.status = 1 
LEFT JOIN tasks t2 ON w.id = t2.worker_id AND t2.status = 2
WHERE 1 
GROUP BY t1.worker_id
,t2.worker_id
ORDER BY w.id

The tasks table is cross-joining against itself when I would rather it wouldn't!

Is there any way to combine these two queries into one such that we can retrieve the counts for multiple task statuses in a single query?

Thanks!

Answer 1

I'm trying to get the number of tasks each worker has with each status code.

SELECT worker_id, status, COUNT(*)
    FROM tasks
    GROUP BY worker_id, status;

That's all.

Answer 2

SELECT w.*,
  SUM(t1.status = 1) AS status_1_count,
  SUM(t1.status = 2) AS status_2_count
FROM workers w
  LEFT JOIN tasks t1 ON w.id = t1.worker_id AND t1.status IN (1, 2) 
GROUP BY w.id
ORDER BY w.id;

Answer 3

I don't have an instance of MySQL here, but I tested this on a t-sql box and it worked.

select distinct(worker_id), 
       (select count(*) from tasks where status = 1) as Status1, 
       (select count(*) from tasks where status = 2) as Status2 
   from tasks;