4.4.12 Aggregate functions

Organization of this subsection
(1) Format
(2) Function
(3) Operands
(4) Syntax rules
(5) Notes
(6) Usage example

(1) Format

aggregate-function::={COUNT'('value-expression|*')'|general-aggregate-function}
   general-aggregate-function::={MAX|MIN|SUM|AVG}'('value-expression')'

(2) Function

Calculates a value using data from multiple rows.

(3) Operands

COUNT

Counts the number rows that were input.

value-expression

Specifies an argument for the aggregate function. For details about value expressions, see 4.4.19 Value expression.

*

Uses all of the rows in the relation.

general-aggregate-function

Specify MAX, MIN, SUM, or AVG. Specify a value expression that includes a column specification in the argument.

MAX
Aggregate function for determining the maximum value.
MIN
Aggregate function for determining the minimum value.
SUM
Aggregate function for determining the sum total.
AVG
Aggregate function for determining the average.

(4) Syntax rules

(5) Notes

If an overflow occurs while executing an aggregate function, processing continues. In this case, an integer will remain in the overflowed state (the higher bytes that do not fit are lost and the lower bytes are used as is) and a floating-point value will become infinity. If an overflow occurs in a floating-point value during an operation involving both a DECIMAL or NUMERIC type value and a floating-point value, however, an error (query group lockup) occurs.

(6) Usage example

Outputs the number of rows for column a of relation s1. The underlined part indicates the aggregate function.

REGISTER QUERY q1 SELECT COUNT(s1.a) AS a1 FROM s1[ROWS 100];