SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Functions and Expressions

HP NonStop SQL/MX Reference Manual—540440-003

9-32

COUNT Function

The COUNT function 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.

COUNT (*)

returns the number of rows in the table specified in the FROM clause of the

SELECT statement that contains COUNT (*). If the result table is empty (that is, no

rows are returned by the query) COUNT (*) returns zero.

ALL | DISTINCT

returns either the number of all rows or the number of distinct rows in the one-

column table derived from the evaluation of expression. The default option is

ALL, which causes duplicate values to be included. If you specify DISTINCT,

duplicate values are eliminated before the COUNT function is applied.

expression

specifies a value expression that determines the values to count. The

expression cannot contain an aggregate function or a subquery. The DISTINCT

clause specifies that the COUNT function operates on distinct values from the one-

column table derived from the evaluation of expression. See Expressions on

page 6-37.

Considerations for COUNT

Operands of the Expression

The operand of COUNT is either * or an expression that includes columns from the

result table specified by the SELECT statement that contains COUNT. However, the

expression cannot include an aggregate function or a subquery. These expressions are

valid:

COUNT (*)

COUNT (DISTINCT JOBCODE)

COUNT (UNIT_PRICE * QTY_ORDERED)

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