AlphaPress Publishing
P.O. Box 5942 Albany, NY 12205
Tel: 518-250-3890
Email: alphapress@hotmail.com  


  
Click to buy this book from Amazon

Section Menu 

News

Pindaro is now working on a comprehensive book on SQL Server 2014.  For any comments you can email the author at alphapress@hotmail.com.

  

 

 

The Count() and Count(*) functions in comparison

To contact the author click here

This article is from the book "Access 2007 Pure SQL

To download the sample database click here


 

The count() and count(*) functions in comparison

Count the number of records in a table using count(*)

Discussion:

In this example, the objective is to clearly understand the differences between Count(), Count(*), and count() DISTINCT so that you can take full advantage of them in your data tasks.  For this purpose, I have temporarily deleted the name of customer Davis from the table tbls_customersag.  Now, we have 20 records in the table with the last name of a customer missing. 

tbls_customersag

PK

LastName

OrderID

ProductID

UnitPrice

Quantity

1

 

4

55

15

3

2

Sterling

60

56

12

5

3

Sterling

60

21

15

4

 Let’s use the count() function first.  As you can see from the result set, the number returned is 19.  This is because we used count() on the lastname field in which a value is missing.  Consequently, while the table contains 20 records, we received only 19.  You do not need to worry about these intricacies if you follow a simple piece of advice:  Any time you work with aggregate functions, calculated fields, or GROUP BY, check your records for null values, and make the necessary edits and replacements (see chapter 23 for details on null values).  Then, you do not need to worry about the intricacies of how aggregate functions or calculated fields behave with missing data. 

Code:

SELECT count(lastname) as NumberofCustomers

FROM tbls_customersag
 

Result:

 

Now, let’s use the count(*) function.  As you can see from the result set, the count(*) function returned the correct result of 20 records in the table.  This does not mean we have 20 unique customers, however, since the count(*) function is generic and not applicable to a particular field. 

Code:

SELECT count(*) as NumberofCustomers

FROM tbls_customersag
 

Result:

Now, let’s use count(*) with a subquery and DISTINCT.  As you can see from the result set, the number of unique customers returned is 10.  We know already, however, that we have 9 unique customers in the table.  This time, the count(*) function returned 10 because it counted the "blank" customer as an additional unique customer. 

Code:

SELECT Count(*) AS NumberofCustomers

FROM

(SELECT DISTINCT lastname FROM tbls_customersag)

Result:

 

Of course, at this point, we might ask which result is correct:  19, 20, or 10?  Well, none of the three numbers is correct.  All of them are wrong for a single reason:  The existence of a null value in the last name field.  You should have two plans of action when it comes to calculated and aggregated fields.  First, you eliminate the null values from the dataset.  If this plan fails, go to plan B, which is to do aggregations and calculations on fields that do not have null values.