Where clause condition on aggregate functions [duplicate]

Question

I have the following simple query,

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 select list but impose certain where conditions on them. Is this done with a CASE() statement somehow? I tried putting Where clauses inside the select list, and that doesn't seem to be allowed. I'm not sure if subqueries are really necessary here, but I dont think so.

For example I want one COUNT() function that only counts issues within a certain range, then another in another range or with other assorted 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 Logon Name.

Thanks.

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

A couple of solutions.

You can take advantage of the fact that SQL does not COUNT NULL values:

  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)
  . . .

Or you can use SUM instead of 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)
  . . .

In either case, you can repeat as many times as you need to.