COUNT Function

Returns the number of rows retrieved by a SELECT statement for a specified table.

Category: Aggregate
Alias: N
Returned data type: BIGINT

Syntax

Form 1:

COUNT(expression)

Form 2:

COUNT(*)

Form 3:

Arguments

expression

specifies any valid SQL expression.

Data type All data types are valid.
See <sql-expression>
FedSQL Expressions

*

returns a count of all rows from the table, including rows that contain null values or SAS missing values.

DISTINCT

returns the number of unique values, excluding null values.

Details

You use the COUNT function in a SELECT statement to return the requested number of rows in a table.
The following list describes what is returned by using the different versions of the COUNT function:
Form 1: COUNT(expression)
returns the number of rows from a table that do not have a null value.
Form 2: COUNT(*)
returns the number of rows in a table.
Form 3: COUNT(DISTINCT expression)
returns the number of rows in expression that have unique values. SAS missing values are included in the results. Null values are not included in the results.
You can use an aggregate function to produce a statistical summary of data in the entire table that is listed in the FROM clause or for each group that is specified in a GROUP BY clause. The GROUP BY clause groups data by a specified column or columns. When you use a GROUP BY clause, the aggregate function in the SELECT clause or in a HAVING clause instructs FedSQL in how to summarize the data for each group. FedSQL calculates the aggregate function separately for each group. If GROUP BY is omitted, then all the rows in the table or view are considered to be a single group.

Example

Table: WORLDTEMPS
The following statements illustrate the COUNT function:
Statements
Results
select count(AvgHigh) from worldtemps;
11
select count(*) from worldtemps;
12
select count(distinct AvgHigh) from worldtemps;
8

See Also

SELECT Statement Clauses:
Last updated: February 23, 2017