aggregate-function::={COUNT'('value-expression|*')'|general-aggregate-function}
general-aggregate-function::={MAX|MIN|SUM|AVG}'('value-expression')'
Calculates a value using data from multiple rows.
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.
Table 4-4 Aggregate function argument types and corresponding result types
No. | Argument type | Aggregate function | ||||
---|---|---|---|---|---|---|
COUNT (argument) | MAX (argument) | MIN (argument) | SUM (argument) | AVG (argument) | ||
1 | INTEGER | INTEGER | INTEGER | INTEGER | INTEGER | INTEGER |
2 | SMALLINT | INTEGER | SMALLINT | SMALLINT | SMALLINT | SMALLINT |
3 | TINYINT | INTEGER | TINYINT | TINYINT | TINYINT | TINYINT |
4 | BIGINT | INTEGER | BIGINT | BIGINT | BIGINT | BIGINT |
5 | DECIMAL | INTEGER | DECIMAL | DECIMAL | DECIMAL | DECIMAL |
6 | NUMERIC | INTEGER | NUMERIC | NUMERIC | NUMERIC | NUMERIC |
7 | REAL | INTEGER | REAL | REAL | REAL | REAL |
8 | FLOAT | INTEGER | FLOAT | FLOAT | FLOAT | FLOAT |
9 | DOUBLE | INTEGER | DOUBLE | DOUBLE | DOUBLE | DOUBLE |
10 | CHAR | INTEGER | CHAR | CHAR | -- | -- |
11 | VARCHAR | INTEGER | VARCHAR | VARCHAR | -- | -- |
12 | DATE | INTEGER | DATE | DATE | -- | -- |
13 | TIME | INTEGER | TIME | TIME | -- | -- |
14 | TIMESTAMP | INTEGER | TIMESTAMP | TIMESTAMP | -- | -- |
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.
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];