SQL Server count(column) query

Question

In sql server, count(column) will count null columns too? I had this question in my exam and I got confused but in the end I gave the answer that yes count(column) will count empty column too. I was just wondering if I was right or wrong. Anyone have any idea about this?

The [documentation](http://technet.microsoft.com/en-us/library/ms175997.aspx) answers this. — , Sep 08 '13 at 08:53
possible duplicate of [count(\*) vs count(column-name) - which is more correct?](http://stackoverflow.com/questions/3003457/count-vs-countcolumn-name-which-is-more-correct) — Mahmoud Gamal, Sep 08 '13 at 08:53
In case of `count(column_name)`, `null` values are not taken into account. — Lion, Sep 08 '13 at 08:56
@Yosi No, that's wrong. But suppose you were right. Then there would be absolutely no point to the column name being specified, since the number of rows does not depend on the column name. — , Sep 08 '13 at 08:57
@user2758530 Also `empty` value is different from `NULL` value, `empty` values will be counted by `COUNT(column)` but `Null` values will be discarded. — Ashish Gaur, Sep 08 '13 at 09:00
@snyder That depends on what you mean by "empty value", it's not a standard term. It could legitimately mean "null value", where an "empty string" is not an "empty value". — , Sep 08 '13 at 09:04

score 7 · Answer 1 · answered Sep 08 '13 at 08:54

7

What stopped you from checking it by yourself?

CREATE TABLE Test (VALUE VARCHAR(10));

INSERT INTO TEST VALUES ('sdfsdf'), ('sdfgsdfg'),( NULL),( NULL);

SELECT COUNT(VALUE) FROM Test

Result:

COUNT(VALUE)
2

So the answer is NO. COUNT(ColumnName) does not count rows with NULL values.

SQLFiddle

answered Sep 08 '13 at 08:54

MarcinJuraszek

124,003
15
196
263

Your answer is incomplete(incorrect). What about `COUNT(*)`? – Hamlet Hakobyan Sep 08 '13 at 08:57
7

@HamletHakobyan Question is about `COUNT(column)`, and so is the answer. – MarcinJuraszek Sep 08 '13 at 08:58
Actually I dont have sql server installed in my laptop. Thats why couldnt check. Anyways Thanks for the answer. – user2758530 Sep 08 '13 at 08:58
1

@user2758530 You can always use tools like [SQLFiddle](http://sqlfiddle.com/). – MarcinJuraszek Sep 08 '13 at 08:59
Ok, but it can cause confusion. – Hamlet Hakobyan Sep 08 '13 at 09:00
*"Actually I dont have sql server installed in my laptop"* `> cinst SqlServer2012Express` [use chocolatey](http://chocolatey.org/packages/SqlServer2012Express) – Chase Florell Oct 08 '13 at 18:47

score 2 · Answer 2 · edited Jun 20 '20 at 09:12

2

Here's what the BOL (Books OnLine) article about COUNT has to say:

COUNT(*) returns the number of items in a group. This includes NULL values and duplicates.

COUNT(ALL expression) evaluates expression for each row in a group and returns the number of nonnull values.

COUNT(DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, nonnull values.

edited Jun 20 '20 at 09:12

Community

1
1

answered Sep 08 '13 at 08:56

SchmitzIT

9,227
9
65
92

1

I think it would be better to also include the bit of the documentation where it says `COUNT(expression)` means `COUNT(ALL expression)`. "ALL: Applies the aggregate function to all values. ALL is the default." I can see someone reading this and thinking "But what about COUNT(columnname)? That isn't in your list." – Sep 08 '13 at 09:01

SQL Server count(column) query

2 Answers2