Count the number of existing and does not exist using MySQL

Question

I am trying to list the entire task with a count of the completed / completed task (in the materials). The problem is that I also want to show the whole task that users have not completed. This query does not list count = 0 (Null). Is there any way to do this?

Required Result:

Date       | title   | completed
2014-05-20 | Case  1 | 45
2014-05-24 | Case 10 | 11
2014-05-20 | Case  2 |  0

I have tried so far:

Select date, title, count(*) as completed
from users u, submissions s, task t
where u.userPK = s.user
and s.task= t.taskPK
group by taskPK
order by completed desc; 

Answer 1

I think you can achieve this using LEFT JOIN:

SELECT date, title, COUNT(u.userPK) completed FROM task t 
LEFT JOIN submissions s ON s.task = t.taskPK 
LEFT JOIN users u ON s.user = u.userPK 
GROUP BY t.taskPK 
ORDER BY completed;

Answer 2

OUTER JOIN. , , , , GROUP BY taskPK, date title.

, , :

SELECT t.date, t.title, count(*) cnt
FROM task t
    LEFT JOIN submissions s ON t.task = s.taskPK
GROUP BY t.date, t.title
ORDER BY cnt DESC

user, , . , .