SQL SERVER – Difference between COUNT(DISTINCT) vs COUNT(ALL)

时间：2011-03-09 来源：Net205 Blog

http://blog.sqlauthority.com/2011/03/08/sql-server-difference-between-countdistinct-vs-countall/

COUNT(DISTINCT)和COUNT(ALL)的区别：前者像select distinct 字段1,字段2 from table一样会去掉重复的，后者则是统计字段的所有数据条数，与Count(字段1)相同。
COUNT(ALL)中的ALL实际上是缺省项，不需要指定。
(注：本人还有点怀疑COUNT(ALL)与COUNT(字段1)是否有区别，待验证)。

Here is the script:

1 SELECT COUNT([Title]) Value
2 FROM [AdventureWorks].[Person].[Contact]
3 GO
4 SELECT COUNT(ALL [Title]) ALLValue
5 FROM [AdventureWorks].[Person].[Contact]
6 GO
7 SELECT COUNT(DISTINCT [Title]) DistinctValue
8 FROM [AdventureWorks].[Person].[Contact]
9 GO

注：select count(字段1) from table，当字段1若有为null的记录，则此记录不在count结果内，要注意。

1 SELECT COUNT(*) FROM table
2 WHERE Column1 IS NULL.
3 
4 select sum(any_null_data_count) from
5 (
6 select case when [any_null_data] is null then 1 else 0 end as any_null_data_count FROM [AdventureWorks].[Person].[Contact] where [any_null_data] is null
7 ) h1