Sql Server equivalent of a COUNTIF aggregate function
You could use a SUM (not COUNT!) combined with a CASE statement, like this: Note: in my own test NULLs were not an issue, though this can be environment dependent. You could handle nulls such as: