Search code examples
sqlms-access-2003

I need to count those that are not selected

I have a MS Access 2003 table of employees. There are two columns. First one is the site name and second column is the status. By the following query string I can easily count those that has the status of "Completed"

SELECT employee.[Site Name], Count(employee.EID) AS [Number of Completed]
FROM employee
WHERE (((employee.[status])="Completed"))
GROUP BY employee.[Site Name]

The question is: There are some sites that are not in the list, those sites that no employee has "Completed" status. I need to list them as well but with the value 0. Any solution in Access 2003 helps me. Thanks.

Solution

  • You want to do conditional aggregation. In MS Access, this looks like:

    SELECT employee.[Site Name],
       SUM(IIF(employee.[status] = "Completed", 1, 0)) AS [Number of Completed]
FROM employee
GROUP BY employee.[Site Name];