Sql Server equivalent of a COUNTIF aggregate function

I usually do what Josh recommended, but brainstormed and tested a slightly hokey alternative that I felt like sharing.

You can take advantage of the fact that COUNT(ColumnName) doesn't count NULLs, and use something like this:

SELECT COUNT(NULLIF(0, myColumn))
FROM AD_CurrentView

NULLIF - returns NULL if the two passed in values are the same.

Advantage: Expresses your intent to COUNT rows instead of having the SUM() notation. Disadvantage: Not as clear how it is working ("magic" is usually bad).

You could use a SUM (not COUNT!) combined with a CASE statement, like this:

SELECT SUM(CASE WHEN myColumn=1 THEN 1 ELSE 0 END)
FROM AD_CurrentView

Note: in my own test NULLs were not an issue, though this can be environment dependent. You could handle nulls such as:

SELECT SUM(CASE WHEN ISNULL(myColumn,0)=1 THEN 1 ELSE 0 END)
FROM AD_CurrentView