SQL Equivalent of COUNTIF()

Question

I'm building a query with a GROUP BY clause that needs the ability to count records based only on a certain condition (field value is equal to 1).

SELECT
    UID, COUNT(UID) AS TotalRecords, SUM(ContractDollars) AS ContractDollars,

    --- Get the average of all records that satisfy the condition.
    (COUNTIF(MyColumn, 1) / COUNT(UID) * 100) are 1
FROM 
    dbo.AD_CurrentView
GROUP BY
    UID
HAVING
    SUM(ContractDollars) >= 500000

The COUNTIF() line obviously fails since there is no native SQL function called COUNTIF(), but the idea here is to determine the percentage of all rows that have the value '1' for MyColumn.

Any thoughts on how to properly implement this in a MS SQL 2005 environment?

Answer 1

You could do:

select sum(case when myColumn=1 THEN 1 ELSE 0 END)
FROM AD_CurrentView

Edit:

Based on my test NULLS are not an issue but this is environment dependant. You could handle nulls such as:

select sum(case when isnull(myColumn,0)=1 THEN 1 ELSE 0 END)
FROM AD_CurrentView

Answer 2

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).

Answer 3

I would use this syntax. It achives the same as Josh and Chris's suggestions, but with the advantage it is ANSI complient and not tied to a particular database vendor.

select count(case when myColumn = 1 then 1 else null end)
from   AD_CurrentView