Need selective count within grouping, how?

[Guest]

I have the following SQL:
SELECT TCreator AS Agent, Datepart('yyyy',Opendate) & '-' &
DatePart('m',opendate) AS [Month], "Other" AS Assigned, Count(ContactVia) AS
CV, count(*) AS Numb
FROM tblCSTickets
WHERE CloseDate IS NOT NULL and LastAssignedTo IS NOT NULL and
LastAssignedTo <> "Customer Support"
GROUP BY TCreator, Datepart('yyyy',Opendate) & '-' & DatePart('m',opendate);

However, I need the count for 'ContactVia' to only count those rows where
the value is '1'. I still need all rows grouped. Can this be done in a single
query?

 

[Marshall Barton]

I have the following SQL:
SELECT TCreator AS Agent, Datepart('yyyy',Opendate) & '-' &
DatePart('m',opendate) AS [Month], "Other" AS Assigned, Count(ContactVia) AS
CV, count(*) AS Numb
FROM tblCSTickets
WHERE CloseDate IS NOT NULL and LastAssignedTo IS NOT NULL and
LastAssignedTo <> "Customer Support"
GROUP BY TCreator, Datepart('yyyy',Opendate) & '-' & DatePart('m',opendate);

However, I need the count for 'ContactVia' to only count those rows where
the value is '1'. I still need all rows grouped. Can this be done in a single
query?

Use any of these expressions:

Count(IIf(ContactVia=1, 1, Null)) AS CV

Sum(IIf(ContactVia=1, 1, 0)) AS CV

Abs(Sum(ContactVia=1)) AS CV