COUNT

COUNT is an aggregate function that counts the number of rows accessed in an expression (see Aggregates (Set Functions)). COUNT is allowed on all types of expressions.

Syntax

COUNT ( [ DISTINCT | ALL ] Expression ) 

The DISTINCT qualifier eliminates duplicates. The ALL qualifier retains duplicates. ALL is assumed if neither ALL nor DISTINCT is specified. For example, if a column contains the values 1, 1, 1, 1, and 2, COUNT(col) returns a greater value than COUNT(DISTINCT col).

Only one DISTINCT aggregate expression per SelectExpression is allowed. For example, the following query is not allowed:

-- query not allowed 
SELECT COUNT (DISTINCT flying_time), SUM (DISTINCT miles)
FROM Flights

An Expression can contain multiple column references or expressions, but it cannot contain another aggregate or subquery. If an Expression evaluates to NULL, the aggregate is not processed for that value.

The resulting data type of COUNT is BIGINT.

Example

-- Count the number of countries in each region,
-- show only regions that have at least 2 
SELECT COUNT (country), region
FROM Countries
GROUP BY region
HAVING COUNT (country) > 1