count

Description

Returns the number of (distinct) non-null values, or the number of selected rows as an integer.

Syntax

count([all | distinct] expression)

Parameters

all

applies count to all values. all is the default.

distinct

eliminates duplicate values before count is applied. distinct is optional.

expression

is a column name, constant, function, any combination of column names, constants, and functions connected by arithmetic or bitwise operators, or a subquery. With aggregates, an expression is usually a column name. For more information, see “Expressions”.

Examples

Example 1

Finds the number of different cities in which authors live:

select count(distinct city) 
from authors 

Example 2

Lists the types in the titles table, but eliminates the types that include only one book or none:

select type 
from titles 
group by type 
having count(*) > 1 

Usage

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute count.

See also

Commands compute clause, group by and having clauses, select, where clause