Presents your 
SQL SERVER E-NEWSLETTER for May 15, 2003

<------------------------------------------->

TIPS FOR KEEPING THE COUNT FUNCTION ACCURATE

COUNT is an aggregate, mathematical function that returns the total
number of items in a group in the form of an integer, or int data type. 
On face value that appears simple; however, add the nulls and duplicate 
values that appear in columns, and then using the COUNT function is a little
more complicated.

First, you need to determine what it is that you want to count. Are you
looking for the total number of items in a group or the total number of
distinct items in a group?

The syntax for the COUNT function is as follows:

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

The default ALL argument returns the total number of all values,
DISTINCT specifies that COUNT return the number of unique non-null values, and
expression allows you to get the total number of specific data types of a
unique identifier, text, image, or ntext. The * specifies that the total
returned contains all rows in a table--including rows with duplicates 
and null values. The * cannot be used with DISTINCT.

For example, the following COUNT returns the total number of rows in the
authors table in the pubs database:

USE pubs
GO
SELECT COUNT(*)
FROM authors
GO

Result:
23

Using this example, the query returns a total of 23. Of those 23 rows,
let's determine how many distinct cities are listed in the city column:

USE pubs
GO
SELECT COUNT (DISTINCT City)
FROM authors
GO

Result:
16

The result is 16, meaning that of those 23 rows, 16 have distinct city listings.

You can use COUNT with other aggregates. Here we'll query the titles
table of the pubs database to find the number of books and their average
price where the advance was greater than $1,000.

USE pubs
GO
SELECT COUNT(*), AVG(price)
FROM titles
WHERE advance > $1000
GO

Result:
15     14.4180

The result returns a total number of 15 books with an average price of
$14.42.

----------------------------------------