Need selective count within grouping, how?

M

mscertified

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?
 
M

Marshall Barton

mscertified said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top