How to include zero-count results in query

Question

I have these two tables:

Operators:

Id Nome
--+----
 1 JDOE
 2 RROE
 3 MMOE

Calls:

Id CallDate OpId
--+--------+----
 1 20161228    2
 2 20161228    3
 3 20161228    2
 4 20161228    3
 5 20170104    1
 6 20170104    2
 7 20170104    1

And this query:

SELECT Operators.id, Operators.Nome, Count(Calls.OpId) AS CountCalls
FROM Operators LEFT JOIN Calls ON Operators.id = Calls.OpId
GROUP BY Calls.CallDate, Operators.id, Operators.Nome
HAVING Calls.CallDate=20170104;

Which returns:

Id Nome CountCalls
--+----+----------
 1 JDOE          2
 2 RROE          1

How can I make it return this, instead?

Id Nome CountCalls
--+----+----------
 1 JDOE          2
 2 RROE          1
 3 MMOE          0

That is, how to include in any query also the zero results from main table which has no occurrence in left joined table, at least in the data slice defined by the query filtering criteria?

This is Access 2013.

I've read this answer but couldn't see how it is different from what I'm doing.

Do not reference any LEFT JOINed table column in WHERE clause predicates disallowing NULL value. — Serg, Jul 07 '17 at 13:31

score 9 · Accepted Answer · answered Jul 07 '17 at 13:35

Because you have a reference to Calls.CallDate in your HAVING clause, you are removing operators where there are no calls. If there were no calls, then CallDate would be NULL, and NULL=20170104 is not true, so these rows are excluded. You need to move this predicate to your join clause:

SELECT Operators.id, Operators.Nome, Count(Calls.OpId) AS CountCalls
FROM Operators LEFT JOIN Calls ON (Operators.id = Calls.OpId AND Calls.CallDate=20170104)
GROUP BY Calls.CallDate, Operators.id, Operators.Nome;

You also don't need to group by Calls.CallDate, since you only have one anyway, so you can just use:

SELECT Operators.id, Operators.Nome, Count(Calls.OpId) AS CountCalls
FROM Operators LEFT JOIN Calls ON (Operators.id = Calls.OpId AND Calls.CallDate=20170104)
GROUP BY Operators.id, Operators.Nome;

As an aside HAVING is the wrong operator. HAVING is for filtering aggregates, since you are not filtering an aggregate, you should simply use WHERE

SELECT Operators.id, Operators.Nome, Count(Calls.OpId) AS CountCalls
FROM Operators LEFT JOIN Calls ON Operators.id = Calls.OpId
WHERE Calls.CallDate=20170104
GROUP BY Calls.CallDate, Operators.id, Operators.Nome;

You would use HAVING if you wanted to fliter on CountCalls, e.g if you only wanted operators that had made more than 1 call you might use:

SELECT Operators.id, Operators.Nome, Count(Calls.OpId) AS CountCalls
FROM Operators LEFT JOIN Calls ON Operators.id = Calls.OpId
WHERE Calls.CallDate=20170104
GROUP BY Calls.CallDate, Operators.id, Operators.Nome
HAVING Count(Calls.OpId) > 1;

This would only return

Id Nome CountCalls
--+----+----------
 1 JDOE          2

Just an observation: since these changes are done, MS-Access will refuse to show this query in design mode, it will only be available as SQL or result view. Apparently, its interface can't deal with this more complex Join clause. Shame on MS about this... — VBobCat, Jul 07 '17 at 13:49
Well, that's true for any atypical join, and I wouldn't shame MS for it. They just can't represent it properly in design view, and there shouldn't be hidden elements to a query that aren't represented there. — Erik A, Jul 07 '17 at 14:13

score 2 · Answer 2 · answered Jul 07 '17 at 13:35

Try below query:

SELECT o.id, o.name, count(c.opid) as countcalls
FROM Operators o LEFT JOIN calls c ON o.id = c.opid AND c.calldate=20170104
GROUP BY c.calldate, o.id, o.name
ORDER BY o.id;

Output:

+------+------+------------+
| id   | name | countcalls |
+------+------+------------+
|    1 | JDOE |          2 |
|    2 | RROE |          1 |
|    3 | MMOE |          0 |
+------+------+------------+

How to include zero-count results in query

2 Answers2