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

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 (http://blog.SQLAuthority.com)

About these ads

14 thoughts on “SQL SERVER – Difference between COUNT(DISTINCT) vs COUNT(ALL)

  1. 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

    Like

    • 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.

      Like

      • 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

        Like

  2. @Yuvraj: I think, this might work:

    SELECT COUNT(*)
    FROM
    [AdventureWorks].[Person].[Contact]
    WHERE [any_null_data] IS NULL

    Like

  3. 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 .

    Like

  4. 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

    Like

  5. 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.

    Like

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #019 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s