filtering inside COUNT in sql server

Question

Possible Duplicate:
SQL Equivalent of COUNTIF()

Can I incorporate some kind of filtering mechanism inside COUNT itself, which I dont want to use in WHERE or JOINs of the query (since it is a part of large query in which there are other columns too which I dont want to get affected by where and join conditions).

For example I can use case inside SUM

SUM(CASE WHEN work_status IN ('V','L') THEN shift_total_hours ELSE 0 END),

Can I do something like this in COUNT too, so that I would get count for particular rows only

something like this:

COUNT(CASE WHEN work_status IN ('V','L') THEN <should come in count> ELSE <exclude from count> END)

many thanks.

score 35 · Accepted Answer · answered Mar 25 '11 at 15:38

35

You can just use

COUNT(CASE WHEN work_status IN ('V','L') THEN 1 END)

For rows not matching the specified condition the CASE expression returns NULL and COUNT only counts NOT NULL values

answered Mar 25 '11 at 15:38

Martin Smith

438,706
87
741
845

3

+1. Put another way, if the ELSE branch is left out, `ELSE NULL` is implied. – Andriy M Mar 25 '11 at 16:11

score 8 · Answer 2 · answered Mar 25 '11 at 15:30

8

This should do what you want:

SUM(CASE WHEN work_status IN ('V','L') THEN 1 ELSE 0 END)

Although it is using the SUM aggregate function, it is effectively a conditional count because for each row, you are adding either 1 or 0 to the sum.

answered Mar 25 '11 at 15:30

Greg

33,450
15
93
100

1

Doesn't work with DISTINCT though. – Ian Warburton Jun 29 '18 at 15:49

filtering inside COUNT in sql server

2 Answers2