[bmeansdfw]

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?

[Pat Phelan]

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

-PatP

[bmeansdfw]

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!

[andrewst]

Like this:

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

[andrewst]

Or still using COUNT:

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