TEXTAGG(col1, col2 as name DELIMITER '|' HEADER ORDER BY col1)
Aggregate functions take sets of values from a group produced by an explicit or implicit GROUP BY and return a single scalar value computed from the group.
You can use the following aggregate functions in Teiid:
- COUNT(*)
-
Count the number of values (including nulls and duplicates) in a group. Returns an integer - an exception will be thrown if a larger count is computed.
- COUNT(x)
-
Count the number of values (excluding nulls) in a group. Returns an integer - an exception will be thrown if a larger count is computed.
- COUNT_BIG(*)
-
Count the number of values (including nulls and duplicates) in a group. Returns a long - an exception will be thrown if a larger count is computed.
- COUNT_BIG(x)
-
Count the number of values (excluding nulls) in a group. Returns a long - an exception will be thrown if a larger count is computed.
- SUM(x)
-
Sum of the values (excluding nulls) in a group.
- AVG(x)
-
Average of the values (excluding nulls) in a group.
- MIN(x)
-
Minimum value in a group (excluding null).
- MAX(x)
-
Maximum value in a group (excluding null).
- ANY(x)/SOME(x)
-
Returns TRUE if any value in the group is TRUE (excluding null).
- EVERY(x)
-
Returns TRUE if every value in the group is TRUE (excluding null).
- VAR_POP(x)
-
Biased variance (excluding null) logically equals(sum(x^2) - sum(x)^2/count(x))/count(x); returns a double; null if count = 0.
- VAR_SAMP(x)
-
Sample variance (excluding null) logically equals(sum(x^2) - sum(x)^2/count(x))/(count(x) - 1); returns a double; null if count < 2.
- STDDEV_POP(x)
-
Standard deviation (excluding null) logically equals SQRT(VAR_POP(x)).
- STDDEV_SAMP(x)
-
Sample standard deviation (excluding null) logically equals SQRT(VAR_SAMP(x)).
- TEXTAGG(expression [as name], … [DELIMITER char] [QUOTE char | NO QUOTE] [HEADER] [ENCODING id] [ORDER BY …])
-
CSV text aggregation of all expressions in each row of a group. When DELIMITER is not specified, by default comma(,) is used as delimiter. All non-null values will be quoted. Double quotes(") is the default quote character. Use QUOTE to specify a different value, or NO QUOTE for no value quoting. If HEADER is specified, the result contains the header row as the first line - the header line will be present even if there are no rows in a group. This aggregation returns a blob.
-
XMLAGG(xml_expr [ORDER BY …]) – XML concatenation of all XML expressions in a group (excluding null). The ORDER BY clause cannot reference alias names or use positional ordering.
-
JSONARRAY_AGG(x [ORDER BY …]) – creates a JSON array result as a Clob including null value. The ORDER BY clause cannot reference alias names or use positional ordering. For more information, see JSONARRAY function.
jsonArray_Agg(col1 order by col1 nulls first)
could return
[null,null,1,2,3]
-
STRING_AGG(x, delim) – creates a lob results from the concatenation of x using the delimiter delim. If either argument is null, no value is concatenated. Both arguments are expected to be character (string/clob) or binary (varbinary, blob), and the result will be CLOB or BLOB respectively. DISTINCT and ORDER BY are allowed in STRING_AGG.
string_agg(col1, ',' ORDER BY col1 ASC)
could return
'a,b,c'
-
LIST_AGG(x [, delim]) WITHIN GROUP (ORDER BY …) – a form of STRING_AGG that uses the same syntax as Oracle. Here
x
can be any type that can be converted to a string. Thedelim
value, if specified, must be a literal, and theORDER BY
value is required. This is only a parsing alias for an equivalentstring_agg
expression.
listagg(col1, ',') WITHIN GROUP (ORDER BY col1 ASC)
could return
'a,b,c'
-
ARRAY_AGG(x [ORDER BY …]) – Creates an array with a base type that matches the expression x. The ORDER BY clause cannot reference alias names or use positional ordering.
-
agg([DISTINCT|ALL] arg … [ORDER BY …]) – A user defined aggregate function.
-
Some aggregate functions may contain a keyword 'DISTINCT' before the expression, indicating that duplicate expression values should be ignored. DISTINCT is not allowed in COUNT(*) and is not meaningful in MIN or MAX (result would be unchanged), so it can be used in COUNT, SUM, and AVG.
-
Aggregate functions cannot be used in FROM, GROUP BY, or WHERE clauses without an intervening query expression.
-
Aggregate functions cannot be nested within another aggregate function without an intervening query expression.
-
Aggregate functions may be nested inside other functions.
-
Any aggregate function may take an optional FILTER clause of the form
FILTER ( WHERE condition )
The condition may be any boolean value expression that does not contain a subquery or a correlated variable. The filter will logically be evaluated for each row prior to the grouping operation. If false the aggregate function will not accumulate a value for the given row.
For more information on aggregates, see the sections on GROUP BY or HAVING.