COUNT returns the number of items in a group. The group is determined by the FROM clause that follows and may represent all records or distinct records. All is the default.

Basic Syntax

In its most basic form, the COUNT returns the total count of all records returned by the rest of the query. Using the asterisk (*) to represent the entire group is common because the database engine is able to easily optimize the query.

COUNT (*) FROM <from-clause>

Distinct Results

Use DISTINCT to return the number of unique non-null values returned by rest of the query.

COUNT ( DISTINCT <expression> ) FROM <from-clause>

Examples

The following field example from a Households View will count the number of Contacts in each Household:

(SELECT COUNT(C.Contact_ID) 
  FROM Contacts C 
  WHERE C.Household_ID = Households.Household_ID) AS [Contact Count]

View Examples

• Groups Over Capacity
• Groups Under Capacity
• Events Needing Approval
• Group Counts

Resources

• MSDN — COUNT Function