//
you're reading...
SQL Server

T-SQL COUNT(DISTINCT) vs COUNT(ALL)

 

What is the difference between COUNT(DISTINCT) vs COUNT(ALL)?

Someone recently asked me the aforementioned question. Seeing how it appeared to be intuitive but never having used the Count(ALL) I ran the following query from the adventure works database.

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.

As you will clearly notice from the result set, the COUNT (ALL ColumnName) and COUNT(ColumnName) function exactly the same. In reality the “ALL” is actually  the default option for the COUNT function and does not need to be specified. The ALL keyword includes all the non-NULL values. DISTINCT is just like is sounds and will return a COUNT of the unique values in the set.

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments yet.

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

%d bloggers like this: