Sql Server equivalent of a COUNTIF aggregate function

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

SELECT UID, COUNT(UID) AS TotalRecords, SUM(ContractDollars) AS ContractDollars, (COUNTIF(MyColumn, 1) / COUNT(UID) * 100) -- Get the average of all records that 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?

10 Answers

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 

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

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 

How about

SELECT id, COUNT(IF status=42 THEN 1 ENDIF) AS cnt FROM table GROUP BY table 

Shorter than CASE :)

Works because COUNT() doesn't count null values, and IF/CASE return null when condition is not met and there is no ELSE.

I think it's better than using SUM().

Adding on to Josh's answer,

SELECT COUNT(CASE WHEN myColumn=1 THEN AD_CurrentView.PrimaryKeyColumn ELSE NULL END) FROM AD_CurrentView 

Worked well for me (in SQL Server 2012) without changing the 'count' to a 'sum' and the same logic is portable to other 'conditional aggregates'. E.g., summing based on a condition:

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

Not product-specific, but the SQL standard provides

SELECT COUNT() FILTER WHERE <condition-1>, COUNT() FILTER WHERE <condition-2>, ... FROM ... 

for this purpose. Or something that closely resembles it, I don't know off the top of my hat.

And of course vendors will prefer to stick with their proprietary solutions.

It's 2022 and latest SQL Server still doesn't have COUNTIF (along with regex!). Here's what I use:

-- Count if MyColumn = 42 SELECT SUM(IIF(MyColumn = 42, 1, 0)) FROM MyTable 

IIF is a shortcut for CASE WHEN MyColumn = 42 THEN 1 ELSE 0 END.

Why not like this?

SELECT count(1) FROM AD_CurrentView WHERE myColumn=1 

I had to use COUNTIF() in my case as part of my SELECT columns AND to mimic a % of the number of times each item appeared in my results.

So I used this...

SELECT COL1, COL2, ... ETC (1 / SELECT a.vcount FROM (SELECT vm2.visit_id, count(*) AS vcount FROM dbo.visitmanifests AS vm2 WHERE vm2.inactive = 0 AND vm2.visit_id = vm.Visit_ID GROUP BY vm2.visit_id) AS a)) AS [No of Visits], COL xyz FROM etc etc 

Of course you will need to format the result according to your display requirements.

SELECT COALESCE(IF(myColumn = 1,COUNT(DISTINCT NumberColumn),NULL),0) column1, COALESCE(CASE WHEN myColumn = 1 THEN COUNT(DISTINCT NumberColumn) ELSE NULL END,0) AS column2 FROM AD_CurrentView