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:

FunctionDescription
`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

``````SELECT
TEXTAGG( a.FirstName AS FirstName, a.LastName AS LastName
DELIMITER ',' HEADER ORDER BY a.LastName )
AS "Name_List"
WHERE a.departmentID = 3;``````
SQL
`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
``````SELECT XMLELEMENT("Department",
XMLAGG(XMLELEMENT("Names",
a.FirstName||' '|| a.LastName)
ORDER BY LastName))
as "Name_List"
WHERE a.departmentID = 3;``````
SQL
`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.
``````SELECT jsonArray_Agg(col1 order by col1 nulls first)

// The return may be as follows:
[null,null,1,2,3]``````
SQL
`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).
``````string_agg(col1, ',' ORDER BY col1 ASC)

// The return may be as follows:
'a,b,c'``````
SQL
`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.

``````SELECT array_agg(a ORDER BY b DESC) FROM test_tables.test_a;;

SELECT array_agg("name") from "SYSADMIN.Connections" ;;``````
SQL

`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:

``FILTER ( WHERE condition )``
CODE

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`.