> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://ocient.ferndocs.com/llms.txt.
> For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://ocient.ferndocs.com/_mcp/server.

# Sorted Aggregate Functions

> Learn about sorted aggregate functions in Ocient for ordered data analysis.

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**](/guides/load-data/transform-data-in-data-pipelines) function during data load.

**Syntax**

```sql
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"`                                                                                                                                                                                                                                                                                                                                                                                                   |

## Related Links

[**Aggregate Functions**](/guides/sql-reference/aggregate-functions)

[**Array Data Transformation Functions**](/guides/load-data/transform-data-in-data-pipelines)