SQL in a Nutshell, 3rd Edition

Name

COUNT

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

ANSI SQL Standard Syntax

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

COUNT(*)

Counts all the rows in the target table, regardless of whether 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 are 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 ANSI SQL syntax of COUNT.

Oracle

Oracle supports the ANSI SQL 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 “ANSI SQL Window Functions.”

Examples

This query counts all the 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