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,
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

Count

Hello,

I need to retrieve all records from a table named Blogs and the number
of Posts associated with which Blog giving the name NumberOfPosts to
that extra column.

I have the following:

SELECT b.*, p.COUNT(*) AS NumberOfPosts
FROM dbo.Blogs b
LEFT JOIN dbo.Posts p
ON b.BlogId = p.BlogId

I get the error:
Incorrect syntax near '*'.

Could someone, please, help me out?

Thanks,
Miguel

Try this:

SELECT b.*, COUNT(p.*) AS NumberOfPosts
FROM dbo.Blogs b
LEFT JOIN dbo.Posts p
ON b.BlogId = p.BlogId

|||

I think you need agroup by clause to use the Count(*), do you have to return all the columns in dbo.Blogs?

|||

And change the top of your query to...

SELECT b.*, COUNT(*) AS NumberOfPosts