SQL
Syntax
<div id="rrdiagram"></div>  
Aggregates are functions that *combine* multiple rows into a single value. Aggregates are different from scalar functions and window functions because they change the cardinality of the result. As such, aggregates can only be used in the `SELECT` and `HAVING` clauses of a SQL query.  
#### `DISTINCT` Clause in Aggregate Functions {#docs:sql:functions:aggregates::distinct-clause-in-aggregate-functions}  
When the `DISTINCT` clause is provided, only distinct values are considered in the computation of the aggregate. This is typically used in combination with the `count` aggregate to get the number of distinct elements; but it can be used together with any aggregate function in the system.  
#### `ORDER BY` Clause in Aggregate Functions {#docs:sql:functions:aggregates::order-by-clause-in-aggregate-functions}  
An `ORDER BY` clause can be provided after the last argument of the function call. Note the lack of the comma separator before the clause.  
```sql
SELECT ⟨aggregate_function⟩(⟨arg⟩, ⟨sep⟩ ORDER BY ⟨ordering_criteria⟩);
```  
This clause ensures that the values being aggregated are sorted before applying the function.
Most aggregate functions are order-insensitive, and for them this clause is parsed and discarded.
However, there are some order-sensitive aggregates that can have non-deterministic results without ordering, e.g., `first`, `last`, `list` and `string_agg` / `group_concat` / `listagg`.
These can be made deterministic by ordering the arguments.  
For example:  
```sql
CREATE TABLE tbl AS
SELECT s FROM range(1, 4) r(s);

SELECT string_agg(s, ', ' ORDER BY s DESC) AS countdown
FROM tbl;
```  
| countdown |
|-----------|
| 3, 2, 1   |