COUNT, IIF usage for counting records that also have a specific field value matched

Question

Using MS Access and I have two tables, one is categories and the other is content.

My initial SQL statement, included below,takes a count of each content associated to a category and returns the count associated with each category.

So for each CATEGORY, I'm simply trying to return another count in which I count CONTENT that have a specific user level and are not deleted for each CATEGORY.

Below is what I am struggling with as I am not certain you can actually use COUNT like this.

 COUNT(IIf([CONTENT.isDeleted]=0,1,0)) - COUNT(IIf([CONTENT.userLevel]=2)) AS userLevelCount

This is the full select statement with my addition but not working.

 SELECT

 CATEGORY.categoryId,
 CATEGORY.categoryTitle,
 CATEGORY.categoryDate,
 CATEGORY.userLevel,
 Last(CONTENT.contentDate) AS contentDate,
 CATEGORY.isDeleted AS categoryDeleted,
 COUNT(IIf([CONTENT.isDeleted]=0,1,0)) AS countTotal,
 COUNT(IIf([CONTENT.isDeleted]=1,[CONTENT.contentID],Null))           AS countDeleted,
 COUNT([CONTENT.categoryId]) -      COUNT(IIf([CONTENT.isDeleted]=1,[CONTENT.contentID],Null))AS countDifference,


 COUNT(IIf([CONTENT.isDeleted]=0,1,0)) - COUNT(IIf([CONTENT.userLevel]=2)) AS userLevelCount

 FROM CATEGORY

 LEFT JOIN CONTENT ON
 CATEGORY.categoryId = CONTENT.categoryId

 GROUP BY
 CATEGORY.categoryId,
 CATEGORY.categoryTitle,
 CATEGORY.categoryDate,
 CATEGORY.userLevel,
 CATEGORY.isDeleted
 HAVING (((CATEGORY.isDeleted)=0))

 ORDER BY

 CATEGORY.categoryTitle

Answer 1

you should be able to use the following

SUM(IIf([CONTENT.isDeleted]=0,1,0)) - COUNT(IIf([CONTENT.userLevel]=2,1,NULL)) AS userLevelCount

COUNT will not count NULL, but it will count zero. SUM will calculate the sum of all 1's - that's a second way of achieving the same.

IIF exists in the newer SQL versions

Answer 2

I believe I found the solution

 Count(IIf([CONTENT.userLevel]=2,[CONTENT.contentID],Null)) AS countDifference2

This will return the count difference for CONTENT for each CATEGORY that isn't deleted and has a specific user level.