NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-125
COUNT Function
An explicit correlation name is an SQL identifier associated with a table or view in the
FROM clause of a SELECT statement, in the select-statement of an INSERT
statement, or in a subquery. The name must be unique within the FROM clause.
An explicit correlation name is known only to the statement in which you define it. You
can use the same identifier as a correlation name in another statement.
A table or view reference that has no explicit correlation name has an implicit
correlation name. The implicit correlation name is the table or view name without the
optional subvolume, volume, and node qualifiers, or—if the reference to the table or
view is a DEFINE—the portion of the DEFINE name that follows the equals sign (=).
You cannot use an implicit correlation name for a reference that has an explicit
correlation name within the statement.
The following example shows the uses of both explicit and implicit correlation names.
The query refers to two tables (ORDERS and CUSTOMER) that contain columns
named CUSTNUM. In the WHERE clause, one column reference is qualified by an
implicit correlation name (ORDERS) and the other by an explicit correlation name (X):
SELECT ORDERNUM, CUSTNAME FROM ORDERS, CUSTOMER X
WHERE ORDERS.CUSTNUM = X.CUSTNUM and ORDERS.CUSTNUM = 543;
COUNT Function
COUNT is a function that counts the number of rows that result from a query or the
number of rows that contain a distinct value in a specific column.
The result of COUNT is data type LARGEINT. The result can never be null.
*
specifies that COUNT should not exclude null values from the aggregate set. If the
set is empty, COUNT returns zero.
DISTINCT column-name
specifies a set of distinct column values from each row of the result table to
determine COUNT. The column cannot be a column from a view that corresponds to
an expression in the view definition.
Duplicate rows are eliminated only if you specify DISTINCT; otherwise, all rows
are included whether or not you specify ALL.
If you specify DISTINCT in more than one COUNT function in the same statement,
the functions must reference the same column.
Specifying DISTINCT with the COUNT function places no restrictions on the use
of DISTINCT with AVG, SUM, MAX, or MIN.
COUNT { ( * ) }
{ (DISTINCT column-name) }