Where is the condition condition for aggregate functions

Question

I have the following simple request:

SELECT US_LOGON_NAME as Username, 
COUNT(I.IS_ISSUE_NO) as Issues
FROM ISSUES I JOIN USERS U ON I.IS_ASSIGNED_USER_ID = U.US_USER_ID
WHERE I.IS_RECEIVED_DATETIME BETWEEN 20110101000000 AND 20110107000000
GROUP BY U.US_LOGON_NAME; 

Where I want to add additional COUNT () functions to the selection list, but to impose certain conditions on them. Is this done using the CASE () statement in some way? I tried putting Where clauses inside a select list, and this does not seem to be allowed. I'm not sure that subqueries are needed here, but I don't think so.

For example, I need one function COUNT (), which takes into account only problems in a certain range, then another in a different range or in different different conditions, etc.:

 SELECT US_LOGON_NAME as Username, 
 COUNT(I.IS_ISSUE_NO (condition here)
 COUNT(I.IS_ISSUE_NO (a different condition here)

etc...

Still grouped by login.

Thank.

Answer 1

SELECT
  SUM(CASE WHEN I.IS_ISSUE_NO (condition here) THEN 1 ELSE 0 END) AS COND1
  SUM(CASE WHEN I.IS_ISSUE_NO (condition here) THEN 1 ELSE 0 END) AS COND2

Answer 2

.

, SQL COUNT NULL:

  SELECT US_LOGON_NAME as Username, 
  COUNT(CASE WHEN <cond>       THEN I.IS_ISSUE_NO ELSE NULL END)
  COUNT(CASE WHEN <other cond> THEN I.IS_ISSUE_NO ELSE NULL END)
  . . .

SUM COUNT:

  SELECT US_LOGON_NAME as Username, 
  SUM(CASE WHEN <cond>       THEN 1 ELSE 0 END)
  SUM(CASE WHEN <other cond> THEN 1 ELSE 0 END)
  . . .

, .

Answer 3

In the example, the invoice is returned per user, per IssueType.

;
with
q_00 as (
select
      is_issue_no
    , is_assigned_user_id 
    , case
          when is_issue_no between  1 and 10 then 'A'
          when is_issue_no between 11 and 14 then 'B'
          else  'C'  
      end as IssueType
from Issues 
)
select
      us_logon_name
    , IssueType
    , count(1) as cnt
from q_00  as a
join users as u on a.is_assigned_user_id = u.us_user_id
group by us_logon_name, IssueType
order by us_logon_name, IssueType ;

SQL Server 2005 +