Where clause condition on aggregate functions

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.

score 11 · Answer 1 · answered Jan 11 '11 at 21:46

11

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

answered Jan 11 '11 at 21:46

mechanical_meat

163,903
24
228
223

Does not `COUNT` also count `0`? I thought only `NULL` was not counted. – Kirk Woll Jan 11 '11 at 21:49
1

@Kirk Woll: Where does it say COUNT in his answer? – Mark Byers Jan 11 '11 at 21:50
Yes, but Adam isn't COUNTing, he's SUMming. – Larry Lustig Jan 11 '11 at 21:51
@Mark, nowhere, curse me for lazy reading. :) – Kirk Woll Jan 11 '11 at 21:51

Larry Lustig · Answer 2 · 2011-01-11T22:43:21.647

5

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.

edited Jan 11 '11 at 22:43

answered Jan 11 '11 at 21:46

Larry Lustig

49,320
14
110
160

1

I think your `CASE` expressions are probably invalid syntax because there is no `END`. – Mark Byers Jan 11 '11 at 21:51
I think you are correct, sir. I just updated the syntax. Must stop rushing. – Larry Lustig Jan 11 '11 at 22:43

score 0 · Answer 3 · answered Jan 11 '11 at 22:05

The example returns count 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 +

Where clause condition on aggregate functions

3 Answers3

Linked

Related