Sql Server equivalent of a COUNTIF aggregate function

Question

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?

score 404 · Accepted Answer · edited May 19 '15 at 11:58

404

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

edited May 19 '15 at 11:58

Tim Barrass

4,813
2
29
55

answered Feb 24 '09 at 17:20

JoshBerke

66,142
25
126
164

3

(I know the OP asked about MS SQL, but just a tiny comment for SQLite users doing the same thing) SQLite has no `ISNULL`, instead you can do `CASE WHEN myColumn IS NULL`, or use `ifnull` ( https://stackoverflow.com/a/799406/1861346 ) – Matt Oct 28 '17 at 02:55
2

can you explain why `sum` instead of `count`? – Lei Yang Mar 29 '21 at 09:29
**Note**: if the aggregate function receives no record at all, `SUM()` will return NULL, whereas `COUNT` will return 0. If you need to have an integer result, you should use `COALESCE(SUM(...), 0)`. For example `SELECT SUM(1) FROM any_table WHERE 1 = 0` will not return an integer. – Benoit Jun 29 '22 at 11:27

score 58 · Answer 2 · answered Feb 24 '09 at 17:38

58

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

answered Feb 24 '09 at 17:38

Chris Shaffer

32,199
5
49
61

2

This solution can gives a different answers than the sum when a group only contains nulls it result in 1 instead of 0. – KimvdLinde Dec 15 '15 at 16:08
Old post, but thanks this helped. I extended the magic and got around the "only nulls" problem by adding `ISNULL` as follows: `SELECT COUNT(NULLIF(0, ISNULL(myColumn, 0)))`. Wait, that just looks ugly... – pcdev Jun 22 '17 at 06:58
1

Would be perfect if there was a NULLIFNOT function – qwertzguy Mar 05 '20 at 23:42

score 28 · Answer 3 · answered Mar 03 '10 at 23:25

28

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

answered Mar 03 '10 at 23:25

asgeo1

9,028
6
63
85

2

Chris's answer is Stndard SQL compliant (hint: `NULLIF` is included Standard SQL-92). Josh's answer can be easily transformed into Standard SQL by replacing `isnull` with `COALESCE`. – onedaywhen Oct 31 '11 at 13:28
I actually like this answer best, because it gets the idea of "counting rows" that Chris was showing, but is more extensible, since you can use any comparison operator; not just `=`. I'm using it for "count the number of responses >=2". – Kristen Hammack Jun 07 '17 at 17:45

score 5 · Answer 4 · answered Jul 25 '17 at 11:04

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

score 3 · Answer 5 · answered Oct 29 '15 at 15:54

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

score 3 · Answer 6 · answered May 22 '22 at 13:51

3

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.

answered May 22 '22 at 13:51

Code Different

90,614
16
144
163

Just to note - the difference between IIF and CASE is that IIF will return null if no records meet the criteria, whereas CASE (and also just COUNT()) will return 0 – Scott Dec 15 '22 at 00:14

score 2 · Answer 7 · edited May 05 '21 at 09:34

2

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.

edited May 05 '21 at 09:34

Lukas Eder

211,314
129
689
1,509

answered Sep 05 '14 at 14:34

Erwin Smout

18,113
4
33
52

1

I had never heard of this before, so I looked it up. According to http://modern-sql.com/feature/filter the only major DBMS that actually offers the `FILTER` clause is PostgreSQL, but it is emulated by `CASE` in all of them. – Kristen Hammack Jun 07 '17 at 17:55

score 1 · Answer 8 · answered Oct 30 '14 at 14:46

1

Why not like this?

SELECT count(1)
FROM AD_CurrentView
WHERE myColumn=1

answered Oct 30 '14 at 14:46

Michal

11
1

2

Because he needs a lot more than just the count. He's trying to get rowcount of a part of a group, and then an aggregate of the whole group, which you can't do with a WHERE. – Kristen Hammack Jun 07 '17 at 17:50

score 1 · Answer 9 · answered Nov 27 '17 at 05:41

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.

score -1 · Answer 10 · edited Dec 10 '18 at 16:34

-1

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

edited Dec 10 '18 at 16:34

Adil B

14,635
11
60
78

answered Dec 10 '18 at 16:01

Andres Sarria

11

Sql Server equivalent of a COUNTIF aggregate function

10 Answers10

Linked

Related