CODEWITHSUNDEEP
mysqlsql
Modaresi
Modaresi

Reputation: 233

Count does not behave properly with null values

I want to count the number of projects assigned to each employee. But I also want to include the name of those who are not currently working on any project, obviously the count value should be either blank or null for those. Here is what I have done so far:

Select
lname
, ssn
, COUNT(*) AS projectnum
FROM
employee LEFT OUTER JOIN works_on
ON essn=ssn
GROUP BY 
lname
ORDER BY
projectnum DESC

It works just fine except, in projectnum field, it puts 1 for those who are not working on any project, rather than null, how can I fix that?

Upvotes: 0

Views: 62

Answers (1)

mu is too short
mu is too short

Reputation: 434665

You're counting the wrong thing. Saying count(*) essentially counts the rows returned, you're getting rows with NULL projects (as intended) but count(*) sees those as rows so you get 1. You need to count a column that will be NULL if there is no project and non-NULL if there is:

select ..., count(c) as projectnum ....

where c is some non-NULL column from works_on.

For example, I have these tables in my MySQL sandbox:

mysql> select * from posts;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+

mysql> select * from posts_tags;
+---------+--------+
| post_id | tag_id |
+---------+--------+
|       1 |      1 |
|       1 |      2 |
|       1 |      3 |
|       2 |      2 |
|       2 |      3 |
|       3 |      2 |
+---------+--------+

You can see the difference between count(*) and count(tag_id):

mysql> select posts.id, count(*) from posts left join posts_tags on posts.id = posts_tags.post_id group by posts.id;
+------+----------+
| id   | count(*) |
+------+----------+
|    1 |        3 |
|    2 |        2 |
|    3 |        1 |
|    4 |        1 |
+------+----------+

mysql> select posts.id, count(tag_id) from posts left join posts_tags on posts.id = posts_tags.post_id group by posts.id;
+------+---------------+
| id   | count(tag_id) |
+------+---------------+
|    1 |             3 |
|    2 |             2 |
|    3 |             1 |
|    4 |             0 |
+------+---------------+

Doing a count(*) in the first one gives you a stray 1 for id=4 but count(tag_id) in the second gives you the expected 0.

Upvotes: 2

Related Questions