COUNT

Returns the number of items in a group.

Arguments:

DISTINCT

Specifies that COUNT returns the number of unique values.
DISTINCT can only be used when the query does not make use of the GROUP BY clause.

ALL

Applies the aggregate function to all values. ALL is the default.

*

Specifies that all records should be counted to return the total number of records, including records that contain NULL values.

<field_expr_list>

Specifies that only records for which at least one of the specified field-expressions is non-NULL should be counted.

Return Type:

INTEGER

Remarks:

• Aggregate functions are allowed as field-expressions only in the SELECT, HAVING, and ORDER BY clauses.
• The arguments of an aggregate function can not reference other aggregate functions.
• The arguments of an aggregate function can not reference the following functions:
  • SEQUENCE
  • OUT_ROW_NUMBER
• DISTINCT is allowed in aggregate functions only when there is no GROUP BY clause.

Examples:

A. COUNT(*)

The following query returns the total number of events in the System event log:

SELECT COUNT(*)
FROM System

B. COUNT(DISTINCT)

The following query returns the total number of distinct event source names in the System event log:

SELECT COUNT(DISTINCT SourceName)
FROM System

C. COUNT(*) and GROUP BY

The following query returns the total number of events generated by each event source in the System event log:

SELECT SourceName, COUNT(*)
FROM System
GROUP BY SourceName

D. COUNT( field-expression )

The following query returns the total number of non-null values for the "cs-username" field in the specified IIS W3C log file:

SELECT COUNT(cs-username)
FROM ex040528.log

E. COUNT(*) and WHERE

The following query returns the total number of requests to a page logged in the specified IIS W3C log file:

SELECT COUNT(*)
FROM ex040528.log
WHERE cs-uri-stem = '/home.asp'

F. COUNT(*), GROUP BY, and HAVING

The following query returns the pages in the specified IIS W3C log file that have been requested more than 50 times:

SELECT cs-uri-stem
FROM ex040528.log
GROUP BY cs-uri-stem
HAVING COUNT(*) > 50

See also:

Aggregate Functions

Aggregating Data Within Groups

© 2004 Microsoft Corporation. All rights reserved.