Count the number of exists and not exist using MySQL

Question

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

Can you give an example of your desired output, don't really understand you expected result. — Cyclonecode, May 29 '14 at 00:17
I want to list out the number of completed task, and the task that is not completed. Represented by (0 or null). — kvambaam, May 29 '14 at 01:34

score 1 · Answer 1 · answered May 29 '14 at 01:41

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.

Cyclonecode · Accepted Answer · 2014-05-29T00:35:58.013

0

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;

edited May 29 '14 at 00:35

answered May 29 '14 at 00:13

Cyclonecode

29,115
11
72
93

Please stop encouraging bad `GROUP BY` practice. – Kermit May 29 '14 at 00:20
@Kermit - I just keept the `GROUP` and `ORDER` clauses as they were. Edited my answer though. – Cyclonecode May 29 '14 at 00:28
@Kermit - Also, I'm not really sure why the above group by clause is considered bad practice, can you explain? – Cyclonecode May 29 '14 at 00:38
@KristerAndersson [MySQL GROUP BY behavior](http://stackoverflow.com/a/1646121/679449) and [Why does MySQL add a feature that conflicts with SQL standards?](http://stackoverflow.com/q/7594865/679449) – Kermit May 29 '14 at 00:39

Count the number of exists and not exist using MySQL

2 Answers2