When does COUNT(*) return NULL?

Question

Hello I have a colleague who always writes ISNULL(COUNT(*),0), but I always thought that COUNT(*) could never return NULL.

But then I searched the interwebs and my findings allowed me to write this little piece of code:

create table t1 (
    val1 varchar(50),
)

select count(*) from t1
where val1 like 'abc'
group by val1

Are there any other cases when COUNT(*) returns NULL?

Your sample does not return a NULL for the count. It returns 0 rows. — Joe Stefanelli, Apr 07 '11 at 16:25
possible duplicate of [Does COUNT(*) always return a result?](http://stackoverflow.com/questions/2552086/does-count-always-return-a-result) — Mikael Eriksson, Apr 07 '11 at 17:06
Ok, this may be a bit off the wall, but I found this because my UPDATE x SET y = (SELCT COUNT(*)...) is setting y to null. — Brill Pappin, Jul 18 '18 at 15:04

score 15 · Accepted Answer · answered Apr 07 '11 at 16:23

15

It doesn't return NULL. The GROUP BY in your example makes it return no rows at all, which is not the same as a NULL in a column.

answered Apr 07 '11 at 16:23

Lucero

Just a note. If you were looking to do this in an update statement, for example:

update t2 set col1=select count(*) from t1 where val1 like 'abc' group by val1 the solution would be to wrap that in an IsNull, like this: update t2 set col1=IsNull((select count(*) from t1 where val1 like 'abc' group by val1),0) – Frank Conry Aug 03 '13 at 20:47

score 2 · Answer 2 · answered Apr 07 '11 at 16:25

2

That example doesn't return NULL. It returns no rows at all because of the GROUP BY on an empty set.

COUNT(*) cannot return a NULL. So the ISNULL is unnecessary.

answered Apr 07 '11 at 16:25

Cade Roux

score 0 · Answer 3 · answered Apr 07 '11 at 16:24

0

ISNULL it not needed it will return a number

answered Apr 07 '11 at 16:24

James Kyburz

3 Answers3