Sorted Aggregate Functions

Aggregate functions compute a single result from a group of rows. These aggregate functions support the DISTINCT keyword, which filters out repeated values, and the standard ORDER BY syntax, which dictates the ordering of the aggregation. To sort the elements of an array, use the ARRAY_SORT function during data load.

Syntax

AGGREGATE([DISTINCT] arg1, arg2, ... [ORDER BY ...])

Substitute AGGREGATE with the function name. The ORDER BY syntax clause must be after all arguments to the aggregate function.

Function Name	Syntax	Purpose
ARRAY_AGG	ARRAY_AGG(expr)	Returns an array containing every row from the expression. Example `SELECT ARRAY_AGG(c1 ORDER BY c1 DESC) FROM sys.dummy10` Output: `int[10,9,8,7,6,5,4,3,2,1]`
ARRAY_CONCAT_AGG	ARRAY_CONCAT_AGG(expr)	Returns an array that concatenates arrays across rows. The input argument is a SQL expression. The element type must be consistent across rows. The function ignores NULL inputs and returns NULL only when all inputs are NULL. The `DISTINCT` keyword removes duplicate arrays. To remove duplicate elements from an array, use the `ARRAY_DISTINCT` function during load. The `ORDER BY` syntax controls the order of concatenation across rows. To sort elements in the array, use the `ARRAY_SORT` function during load. Example **** `SELECT ARRAY_CONCAT_AGG(c1 ORDER BY c1 ASC) FROM sys.dummy10` Output: `int[1,2,3,4,5,6,7,8,9,10]`
STRING_AGG	STRING_AGG(expr, delimiter)	Returns a string concatenated from every row from the expression. The delimiter argument is optional. Example `SELECT STRING_AGG(char(c1), '\|' ORDER BY c1 ASC) FROM sys.dummy10` Output: `"1\|2\|3\|4\|5\|6\|7\|8\|9\|10"`

Aggregate Functions

Array Data Transformation Functions