Using COUNT

If you are like me, then you will find yourself using the COUNT function many times throughout the day. I’ll go into my many uses of the count function later, but for now let’s look at the syntax.

There are basically two ways to use COUNT. One is to perform a count of ALL records, and the other is to perform a count of distinct records.

[cc lang=”sql”]
SELECT COUNT(DISTINCT ProductID)
FROM Production.TransactionHistory

SELECT COUNT(*)
FROM Production.TransactionHistory
[/cc]

This is likely the most common use that I use it for. Often I will want to find the cardinality of a specific field within a table. What I mean by that, is to determine how unique the column is when compared to all the rows in the table. That’s what the query above does. Let’s look at the results.

From this we see that the ProductID field is duplicated quite a bit in the TransactionHistory table.