SQL in a Nutshell, 2nd Edition

Name

COUNT

Synopsis

The COUNT function is used to compute the number of rows in an expression.

SQL2003 Syntax

COUNT(*)
COUNT( [ALL|DISTINCT] expression )

COUNT(*)

Counts all the rows in the target table whether or not they include NULLs.

COUNT( [ALL|DISTINCT] expression)

Computes the number of rows with non-NULL values in a specific column or expression. When the keyword DISTINCT is used, duplicate values will be ignored and a count of the distinct values is returned. ALL returns the number of non-NULL values in the expression and is implicit when DISTINCT is not used.

MySQL, PostgreSQL, and SQL Server

All of these platforms support the SQL2003 syntax of COUNT.

DB2 and Oracle

DB2 and Oracle support the ANSI syntax and the following analytic syntax:

COUNT ({*|[DISTINCT] expression}) OVER (window_clause)

For an explanation of the window_clause, see the section later in this chapter titled Section 4.3.

Examples

This query counts all rows in a table:

SELECT COUNT(*) FROM publishers;

The following query finds the number of different countries where publishers are located:

SELECT COUNT(DISTINCT country) "Count of Countries" 
FROM   publishers