SQL count () with a group without returning 0 / null records

Question

For convenience of discussion, consider this base table (Test) in Access ...

ID  division  name   role
1    1        Frank  100
2    2        David  101
3    3        John   101
4    2        Mike   102
5    2        Rob    102
7    3        Dave   102
8    3        Greg   102

I want to calculate users of a specific role in a section. If I do a simple counter (), I get the correct 0:

SELECT COUNT(ID) as ct 
FROM Test 
WHERE role >=101 and division=1;

gives

division   ct
    1       0

However, I want to include the division number in the results (for the sake of further joining, reports, etc.), and it always returns null / no rows instead of division and count 0:

SELECT division, COUNT(ID) as ct 
FROM Test WHERE role >=101 
GROUP BY division 
HAVING division=1;

or

SELECT division, COUNT(ID) as ct 
FROM Test 
WHERE role >=101 AND division=1 
GROUP BY division;

gives

division   ct

I originally came to this because I would also like it to work if the user enters a division that is not in the table (e.g. 4) ...

SELECT division, COUNT(ID) as ct 
FROM Test 
WHERE role >=101 AND division IN (1,2,4) 
GROUP BY division;

gives

division   ct
    2       3

instead

division   ct
    1       0
    2       3
    4       0

Is it impossible to return the division with the count if the counter is 0?

Answer 1

:

SELECT division, ISNULL(ct,0) AS ct
FROM divisionTable
LEFT JOIN
(SELECT division, COUNT(ID) as ct FROM Test WHERE role >=101 GROUP BY division) CountQuery
ON divisionTable.division = CountQuery.division
WHERE divisionTable.division IN (1,2,4)

Answer 2

- , ( ), LEFT ( right) , , .

Answer 3

, Access, , :

SELECT *
FROM Test 
WHERE role >=101 AND division IN (1,2,4) 
UNION
SELECT 1 as ID, 1 as division, 'Dummy' as Name, 100 as role
FROM Dual
UNION
SELECT 1 as ID, 2 as division, 'Dummy' as Name, 100 as role
FROM Dual
UNION
SELECT 1 as ID, 4 as division, 'Dummy' as Name, 100 as role
FROM Dual

( , Dummy . Dual - Oracle . , , , .)

count (ID) ( ) count ( select

SELECT division, count(ID)-1

Answer 4

SQL-, ...

SELECT  division, COUNT(ID) AS ct
FROM    Test
WHERE   role >= 101
GROUP BY ALL division
HAVING  division = 1 ;

Answer 5

Have you tried using count (*) instead of count (ID)?