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

SQL SERVER - Difference between COUNT(DISTINCT) vs COUNT(ALL) TSQL2sDay 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.

SQL SERVER - Difference between COUNT(DISTINCT) vs COUNT(ALL) ALL_Distinct

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)

SQL Scripts
Previous Post
SQL SERVER – Enable PowerPivot Plugin in Excel
Next Post
SQLAuthority News – Stay Connected and Social Media

Related Posts

13 Comments. Leave new

  • Yuvraj Gautam
    March 8, 2011 4:34 pm

    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

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

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

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

      Reply
      • or

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

  • ivan mohapatra
    March 9, 2011 3:12 am

    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 .

    Reply
  • Jes Schultz Borland
    March 10, 2011 7:38 am

    Thank you for the blog Pinal!

    Reply
  • Brijesh Bellur
    March 12, 2011 12:33 pm

    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

    Reply
  • Hi brijesh use below query

    SELECT COUNT(name) AS Expr1, grade
    FROM test
    GROUP BY grade

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

    Reply

Leave a Reply