SQL select COUNT issue

Question

I have a table

num
----
NULL
NULL
NULL
NULL
55
NULL
NULL
NULL
99

when I wrote

select COUNT(*) 
from tbl
where num is null

the output was 7

but when I wrote

select COUNT(num) 
from tbl
where num is null

the output was 0

what's the difference between these two queries ??

A possible duplicate of this question http://stackoverflow.com/questions/3003457/count-vs-countcolumn-name-which-is-more-correct — praveen, Jun 14 '12 at 06:48
possible duplicate of [In SQL, what's the difference between count(column) and count(*)?](http://stackoverflow.com/questions/59294/in-sql-whats-the-difference-between-countcolumn-and-count) — Damien_The_Unbeliever, Jun 14 '12 at 06:51

score 3 · Accepted Answer · edited Jun 14 '12 at 06:50

3

Difference is in the field you select.

When counting COUNT(*) NULL values are taken into account (count all rows returned).

When counting COUNT(num) NULL values are NOT taken into account (count all non-null fields).

That is a standard behavior in SQL, whatever the DBMS used

Source. look at COUNT(DISTINCT expr,[expr...])

edited Jun 14 '12 at 06:50

Elzo Valugi

27,240
15
95
114

answered Jun 14 '12 at 06:48

Arnaud F.

8,252
11
53
102

score 0 · Answer 2 · answered Jun 14 '12 at 06:48

0

count(*) returns number of rows, count(num) returns number of rows where num is not null. Change your last query to select count(*) from test where num is null to get the result you expect.

answered Jun 14 '12 at 06:48

Krzysztof Kozielczyk

5,887
37
28

score 0 · Answer 3 · edited Jun 14 '12 at 09:04

0

Count(*) counts the number of rows, COUNT(num) counts the number of not-null values in column num.

edited Jun 14 '12 at 09:04

shobhit

702
2
9
21

answered Jun 14 '12 at 06:48

Frank Schmitt

30,195
12
73
107

But there are two `NOT-NULL` values there in `num` column so when the output was 0 don't you think that it should be 2 ?? – yogi Jun 14 '12 at 06:51
2

That would be the case if you hadn't added this where clause: where num is null :-) – Frank Schmitt Jun 14 '12 at 07:02

score 0 · Answer 4 · answered Jun 14 '12 at 06:52

0

In second case first count values are eliminated and then where clause comes in picture. While in first case when you are using * row with null is not eliminated.

If you are counting on a coll which contains null and you want rows with null to be included in count than use

 Count(ISNULL(col,0))

answered Jun 14 '12 at 06:52

ejb_guy

1,125
6
6

ISNULL() is non-standard SQL and might or might not work depending on the RDBMS used by the OP. – Frank Schmitt Jun 14 '12 at 07:05

score -1 · Answer 5 · answered Jun 14 '12 at 06:51

-1

Considering the output given above, the result of the query count(num) should be 2.

answered Jun 14 '12 at 06:51

shadab shah

551
1
6
8

Yes that's what bugged me too. But why the output was 0. – yogi Jun 14 '12 at 06:54
2

That would be true if the query didn't contain a where clause selecting only those rows with "where num is null" – Frank Schmitt Jun 14 '12 at 07:03

SQL select COUNT issue

5 Answers5