Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    4

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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It depends on which SQL dialect you are using, but I'd use Sum() combined with CASE instead of Count().

    -PatP

  3. #3
    Join Date
    Jun 2004
    Posts
    4
    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!

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Like this:

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

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Or still using COUNT:

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •