I have a table with a list of clients, some tasks associated with each and the status of each task (open, closed).

I want to return a SINGLE row for each client, along with a COUNT of the number of "open" and "closed" tasks.

The typical count statement I am using is this:

select SiteName, Count(IssueStatus) as cnt, IssueStatus,
from IssueMaster
group by SiteName, IssueStatus

It works fine, however, it returns a row for each combination of client and status. I would like it to return "Client, Count of Open, Count of Closed".

Any ideas?

It depends on which SQL dialect you are using, but I'd use Sum() combined with CASE instead of Count().

-PatP

I'm using MSSQL, but can I SUM a non-numeric column? And do you have an example I could use as a model?

Thanks!

Like this:

SUM (CASE WHEN status='OPEN' THEN 1 ELSE 0 END) AS open_count

Or still using COUNT:

COUNT (CASE WHEN status='OPEN' THEN 1 END) AS open_count