Aggregate Functions

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.

The Data Virtuality Server supports the following aggregate functions:

To view the full table, click the expand button in its top right corner

Function	Description
`COUNT(*)`	Counts number of values (including nulls and duplicates) in a group
`COUNT(x)`	Counts number of values (excluding nulls) in a group
`SUM(x)`	Sum of values (excluding nulls) in a group
`AVG(x)`	Average of 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(FOR (expression [as name], ... [DELIMITER char` `[QUOTE char] [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 a delimiter; Double quotes(`"`) are the default quote character; Use `QUOTE` to specify a different value; All non-null values will be quoted; 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 SQL `SELECT TEXTAGG( a.FirstName AS FirstName, a.LastName AS LastName DELIMITER ',' HEADER ORDER BY a.LastName ) AS "Name_List" FROM data_source.addresses a WHERE a.departmentID = 3;`
`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 SQL `SELECT XMLELEMENT("Department", XMLAGG(XMLELEMENT("Names", a.FirstName\|\|' '\|\| a.LastName) ORDER BY LastName)) as "Name_List" FROM data_source.addresses a WHERE a.departmentID = 3;`
`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. See also the `JSONArray` function. SQL `SELECT jsonArray_Agg(col1 order by col1 nulls first) // The return may be as follows: [null,null,1,2,3]`
`STRING_AGG(x, delim)`	Creates a lob result from the concatenation of x using the delimiter `delim`. If either argument is null, no value is concatenated; Both arguments are expected to be characters (string/clob) or binary (varbinary, blob), and the result will be clob or blob, respectively; Expressions are allowed as delimiters only for DBMSs which have such capability (PostgreSQL and Data Virtuality Server); `DISTINCT` and `ORDER BY` are allowed in `STRING_AGG`; Whether `DISTINCT` is pushed down to underlying DBMS or not, depends on its capabilities (Oracle and Redshift do not allow it). SQL `string_agg(col1, ',' ORDER BY col1 ASC) // The return may be as follows: 'a,b,c'`
`ARRAY_AGG(x [ORDER BY ...])`	Creates an array with a base type matching the expression x. Produces results that depend on the ordering of the input rows; When using such an aggregate, the optional `ORDER BY` clause can be used to specify the desired ordering; The `ORDER BY` clause cannot reference alias names or use positional ordering. SQL `SELECT array_agg(a ORDER BY b DESC) FROM test_tables.test_a;; SELECT array_agg("name") from "SYSADMIN.Connections" ;;`
`AGG(DISTINCT arg ... [ ORDER BY ... ])`	User-defined aggregate function

Syntax Rules

Some aggregate functions may contain the 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 (the result would be unchanged), but it can be used in COUNT, SUM, and AVG.

Some other things to keep in mind when working with aggregate functions:

They cannot be used in FROM, GROUP BY, or WHERE clauses without an intervening query expression;
They cannot be nested within another aggregate function without an intervening query expression;
They may be nested inside other functions;
Any aggregate function may take an optional FILTER clause of the following form:
CODE
```
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 before the grouping operation. If FALSE, the aggregate function will not accumulate a value for the given row;
User-defined aggregate functions need ALL specified if no other aggregate-specific constructs are used to distinguish the function as an aggregate rather than a normal function. For more information on aggregates, see the sections on GROUP BY and HAVING.