var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: count query problem
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,
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".
It depends on which SQL dialect you are using, but I'd use Sum() combined with CASE instead of Count().
I'm using MSSQL, but can I SUM a non-numeric column? And do you have an example I could use as a model?
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