CODEWITHSUNDEEP
mysqlcountgroup-by
kvambaam
kvambaam

Reputation: 496

Count the number of exists and not exist using MySQL

I am trying to list out all task with the count of finished/completed task (In submissions). The problem is that I also want to show all task that no users have finished. This query does not list out count = 0 (Null). Is there any way to do this?

Wanted 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;

Tables

Upvotes: 4

Views: 459

Answers (2)

sgeddes
sgeddes

Reputation: 62831

You need to use an OUTER JOIN to get your desired results. However, considering the previous answer didn't suffice, I would also guess you don't want to GROUP BY the taskPK field, but rather by the date and title fields.

Perhaps this is what you're looking for:

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

I also removed the user table as I'm not sure how it affects the results. If you need it back, just add an additional join.

Upvotes: 1

Cyclonecode
Cyclonecode

Reputation: 29991

I think you should be able to achive this using a 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;

Upvotes: 0

Related Questions