COUNT(field) return 0 if it doesn’t exist

Sounds like a simple thing to do, but I’m struggling with it and probably over thinking it.

I’m basically trying to figure out if a category, and what category that is, that is missing a Job value in the title field… and I’m trying to do it without iteration.

So in this example, it should return something indicating that Strategy doesn’t have a Job in the title field.

My initial though was to just do a COUNT() like in the following example:

SELECT category, COUNT(title)
FROM grid_mgmt
WHERE title = 'Job'
GROUP BY category;

​x
 
SELECT category, COUNT(title)FROM grid_mgmtWHERE title = 'Job'GROUP BY category;​

But then that became apparent it wouldn’t work because there isn’t anything to count.

Any suggestions on how to return a 0 if there is nothing to count, or perhaps a better way of tackling this issue in MS Access with SQL?

Answer

You can use conditional aggregation:

SELECT category, SUM(IIF(title = "Job", 1, 0))
FROM grid_mgmt
GROUP BY category;

 
SELECT category, SUM(IIF(title = "Job", 1, 0))FROM grid_mgmtGROUP BY category;​

Or if you have a separate category table, a LEFT JOIN:

select c.category, count(gm.category)
from category as c left join
     grid_mgmt as gm
     on gm.category = c.category and
        gm.title = "Job"
group by c.category;

 
select c.category, count(gm.category)from category as c left join     grid_mgmt as gm     on gm.category = c.category and        gm.title = "Job"group by c.category;​

Advertisement

Answer