COUNT function [Aggregate]

Counts the number of rows in a group depending on the specified parameters.

Syntax 1
COUNT( 
*
| expression
|  DISTINCT expression 
)
Syntax 2
COUNT( 
{ * | expression }
) OVER ( window-spec )
window-spec : see Syntax 2 instructions in the Remarks section below
Parameters
  • *   Return the number of rows in each group.

  • expression   The expression for which to return the number of rows.

  • DISTINCT expression   The expression for which to return the number of distinct rows.

Remarks

Rows where the value is the NULL value are not included in the count.

Syntax 2 represents usage as a window function in a SELECT statement. As such, elements of window-spec can be specified either in the function syntax (inline), or in conjunction with a WINDOW clause in the SELECT statement. See the window-spec definition provided in WINDOW clause.

For more information about using window functions in SELECT statements, including working examples, see Window functions.

See also
Standards and compatibility
  • SQL/2003   Core feature. Syntax 2 is feature T611.

Example

The following statement returns each unique city, and the number of rows with that city value.

SELECT City, COUNT( * ) FROM Employees GROUP BY City;