filtering inside COUNT in sql server [duplicate]

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.

Answer 1

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

Answer 2

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.