T-SQL coding

Count()

Today I’d like to review the T-SQL command COUNT(). It’s an easy function that is used to count occurrences in the database, but many developers have never explored the subtleties that it brings…so here we go…first the basic syntax:

COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )

COUNT() is a basic function that is available within SQL Server T-SQL. Above is the basic syntax; there is also an “OVER” option that I’ll pass on today. Many developers use this function all the time without knowing the differences between the different syntax options.   What follows is an explanation of each option. Here are some basic SELECT COUNT() queries using the AdventureWorks database:

-- option 1: asterisk
SELECT count(*) 
FROM Sales.Customer;

-- option 2: column name
SELECT count(StoreID)
FROM Sales.Customer;

-- option 3: distinct 
SELECT count(distinct StoreID)
FROM Sales.Customer;

If you execute these 3 samples you will find that each returns a different result; here’s why:

Option 1: COUNT(*). This is the option I see most of the time. What the asterisk means is to count the number of rows. A constant (like the number 1) can also be used here and does the same thing. The “ALL” option is the default and is implied (I don’t know many people that actually code the “ALL”). The other option is “DISTINCT” which is in option 3. (It should be noted that the “*” doesn’t actually select all of the columns so this is one exception to the best practice that you shouldn’t code “SELECT *”).

Option 2: COUNT(column name). This option counts the non-null occurrences of the column name. If your column is defined as NOT NULL then your result will be the same as option 1. This difference becomes apparent if you perform some arithmetic using the count; your result will be different between option 1 and 2 so choose the correct option for your business definition.

Option 3: COUNT(DISTINCT column name). This option adds the DISTINCT clause to the function, and counts the each non-null value in the column once.

One other item to note: when you execute a SELECT COUNT() you will always get a result. With other SELECT statements if there are no results you will get a null set back, but with SELECT COUNT() you will always get a result set (the count will be zero, but you will get a result).

Advertisements

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