This blog post is written in response to the T-SQL Tuesday hosted by Jes Schultz Borland. Earlier today, I was presenting a 45-minute session at the Community College about “The Beginning SQL Server Database”. One of the students asked me the following question.
What is the difference between COUNT(DISTINCT) vs COUNT(ALL)?
I found this question from the student very interesting. He seems to have read the documentation (Book Online) and was then asking me this question.
I always carry laptop which has SQL Server installed. I quickly opened it and ran the following script. After looking at the result, I think it was clear to everybody.
Here is the script:
SELECT COUNT([Title]) Value
FROM [AdventureWorks].[Person].[Contact]
GO
SELECT COUNT(ALL [Title]) ALLValue
FROM [AdventureWorks].[Person].[Contact]
GO
SELECT COUNT(DISTINCT [Title]) DistinctValue
FROM [AdventureWorks].[Person].[Contact]
GO
The above script will give me the following results.
You can clearly notice from the result set that COUNT (ALL ColumnName) is the same as COUNT(ColumnName). The reality is that the “ALL” is actually the default option and it needs not to be specified. The ALL keyword includes all the non-NULL values.
I know this is very simple and may be it does not change how we work; however looking at the whole angle, I really enjoyed the question.
Reference: Pinal Dave (https://blog.sqlauthority.com)
13 Comments. Leave new
Hi pinal , may u plz tell me how can I count for null values , e.g.
SELECT COUNT([any_null_data]) any_null_data_count FROM [AdventureWorks].[Person].[Contact] where [any_null_data] is null
its answer is always zero
is there any another way to solve this problem , I have to do this
select sum(any_null_data_count) from
(
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
) h1
Hi Yuvraj,
You should try using
SELECT COUNT(*) FROM table
WHERE Column1 IS NULL.
If you use the column name in the COUNT function and if you are checking for NULL values in the column, you would get 0 which is what happened in your case.
Or you can use count(1), any constant will work. Also, if you want a distinct that includes nulls, you can use a placeholder like:
SELECT Count(distinct isnull(MyTextFieldName,’NULL’)) FROM MyTable
@Yuvraj: I think, this might work:
SELECT COUNT(*)
FROM
[AdventureWorks].[Person].[Contact]
WHERE [any_null_data] IS NULL
Thanks Janki , but i wanted to query on a null value column,
now i have done it using.
SELECT COUNT(ISNULL([any_null_data],0))
FROM
[AdventureWorks].[Person].[Contact]
WHERE [any_null_data] IS NULL
or
SELECT COUNT(*)
FROM
[AdventureWorks].[Person].[Contact]
WHERE [any_null_data] IS NULL
dear sir,
how can we have a data of all the query which is been executed or run in a database.
and the data should be in row and column format in a table of that database.
the query might be your select,update,delete,insert.
1.loginname
2-loginmachinname-
3-query-
4-datetime-
sir plz help me out .
Thank you for the blog Pinal!
hi Dave,
table1(name,grade)
values:
name | grade
amit a
brij a
clat b
diana c
now i want to count name with dstinct grade
my statment:
select distinct grade,count(*)
from table1
above st. i have written but not to the result
waiting for reply
@Brijesh
Try this
select grade,count(grade)as gcount
from table1 group by grade order by grade
select name,count(distinct grade)
from table1
group by name
Hi brijesh use below query
SELECT COUNT(name) AS Expr1, grade
FROM test
GROUP BY grade
With the example you give can not tell the difference, I’ll leave you an example that shows this:
table:
code | text
1|a
1|a
1|b
2|a
2|b
case 1)
select
text
, count(code) count — or count(*)
group by
text
result:
a|3
b|2
case 2)
select
text
, count(distinct code) count
group by
text
result:
a|2
b|2
“count distinct” count the repeated values as only one.
Greetings from Chile.