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

Could not locate file 'data01_log' in sysfiles

I need to shrink this log file, and setup some maintenance tasks, but I am
getting the above error. Any advice is greatly appreciated.
sp_helpdb output:
DATA01
1 e:\mssql\data\data01.mdf
PRIMARY 4102976 KB Unlimited 10% data only
DATA01_log
2 c:\logfiles\data01_log.ldf
NULL 327296 KB 460800 KB 10% log only
When I run shrinkfile I get this:
dbcc shrinkfile (data01_log,320)
Server: Msg 8985, Level 16, State 1, Line 1
Could not locate file 'data01_log' in sysfiles.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.Hi
If you do a SELECT * from sysfiles , what is the NAME returned ?
Are you then using the exact - case sensitive spelling?
Jack Vamvas
__________________________________________________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"Rick Billingsley" <RickBillingsley@.discussions.microsoft.com> wrote in
message news:35983A24-DD51-43EA-A644-BA857A76F29B@.microsoft.com...
> I need to shrink this log file, and setup some maintenance tasks, but I am
> getting the above error. Any advice is greatly appreciated.
> sp_helpdb output:
> DATA01
> 1 e:\mssql\data\data01.mdf
>
> PRIMARY 4102976 KB Unlimited 10% data only
> DATA01_log
> 2
c:\logfiles\data01_log.ldf
>
> NULL 327296 KB 460800 KB 10% log only
> When I run shrinkfile I get this:
> dbcc shrinkfile (data01_log,320)
> Server: Msg 8985, Level 16, State 1, Line 1
> Could not locate file 'data01_log' in sysfiles.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>|||the select query returns the mastlog. This is not the log I am trying to
shrink/backup.
I believe the problem is that the log I want is on a different logical drive
(c vs. e) than the database.
I have tried running the shrinkfile with the exact case-sensitive spelling
with the same results.|||Change your database context to Data01. The select statement
indicates that you are trying to do this from the master
database. Shrinkfile applies to files in the current
database so you need to be in the appropriate database.
-Sue
On Mon, 23 Jan 2006 08:58:03 -0800, "Rick Billingsley"
<RickBillingsley@.discussions.microsoft.com> wrote:
>the select query returns the mastlog. This is not the log I am trying to
>shrink/backup.
>I believe the problem is that the log I want is on a different logical drive
>(c vs. e) than the database.
>I have tried running the shrinkfile with the exact case-sensitive spelling
>with the same results.