# Set Aggregates and Operators

Return a set without repeating any elements. | |

Test the membership of an element in a set. | |

Merge two sets. | |

Test whether a set is not empty. | |

Coalesce. | |

Test the membership of an element in a set. | |

Return the number of elements in a set. | |

Return the array made from all of the input set elements. | |

Return the sum of the set of numbers. | |

Generalized boolean AND applied to the set of values. | |

Generalized boolean OR applied to the set of values. | |

Return a set of tuples of the form (index, element). | |

Return the smallest value of the input set. | |

Return the greatest value of the input set. | |

Return the arithmetic mean of the input set. | |

Return the sample standard deviation of the input set. | |

Return the population standard deviation of the input set. | |

Return the sample variance of the input set. | |

Return the population variance of the input set. |

Return a set without repeating any elements.

`DISTINCT`

is a set operator that returns a new set where
no member is equal to any other member.

db>

`SELECT DISTINCT {1, 2, 2, 3};`

{1, 2, 3}

Merge two sets.

Since EdgeDB sets are formally multisets, `UNION`

is a *multiset sum*,
so effectively it merges two multisets keeping all of their members.

For example, applying `UNION`

to `{1, 2, 2}`

and
`{2}`

, results in `{1, 2, 2, 2}`

.

If you need a distinct union, wrap it with `DISTINCT`

.

Coalesce.

Evaluate to `A`

for non-empty `A`

, otherwise evaluate to `B`

.

A typical use case of coalescing operator is to provide default values for optional properties.

```
# Get a set of tuples (<issue name>, <priority>)
# for all issues.
SELECT (Issue.name, Issue.priority.name ?? 'n/a');
```

Without the coalescing operator the above query would skip any
`Issue`

without priority.

Test whether a set is not empty.

`EXISTS`

is an aggregate operator that returns a singleton set
`{true}`

if the input set is not empty and returns `{false}`

otherwise.

db>

`SELECT EXISTS {1, 2};`

{true}

Filter the set based on type.

The type filter operator removes all elements from the input set that aren’t of the specified type. Additionally, since it guarantees the type of the result set all the links and properties associated with the specified type can now be used on the resulting expression. This is especially useful in combination with backward links.

Consider the following types:

```
type User {
required property name -> str;
}
abstract type Owned {
required link owner -> User;
}
type Issue extending Owned {
required property title -> str;
}
type Comment extending Owned {
required property body -> str;
}
```

The following expression will get all `Objects`

owned by all users (if there are any):

`SELECT User.<owner;`

By default backward links don’t infer any type information beyond the
fact that it’s an `Object`

. To ensure that this path
specifically reaches `Issue`

the type filter operator must be used:

```
SELECT User.<owner[IS Issue];
# With the use of type filter it's possible to refer to
# specific property of Issue now:
SELECT User.<owner[IS Issue].title;
```

Return the number of elements in a set.

db>

`SELECT count({2, 3, 5});`

{3}

db>

`SELECT count(User); # number of User objects in db`

{4}

Return the sum of the set of numbers.

The result type depends on the input set type. The general rule is
that the type of the input set is preserved (as if a simple
`+`

was used) while trying to reduce the chance of
an overflow (so all integers produce `int64`

sum).

db>

`SELECT sum({2, 3, 5});`

{10}

db>

`SELECT sum({0.2, 0.3, 0.5});`

{1.0}

Generalized boolean `AND`

applied to the set of *values*.

The result is `true`

if all of the *values* are `true`

or the
set of *values* is `{}`

. Return `false`

otherwise.

db>

`SELECT all(<bool>{});`

{true}

db>

`SELECT all({1, 2, 3, 4} < 4);`

{false}

Generalized boolean `OR`

applied to the set of *values*.

The result is `true`

if any of the *values* are `true`

. Return
`false`

otherwise.

db>

`SELECT any(<bool>{});`

{false}

db>

`SELECT any({1, 2, 3, 4} < 4);`

{true}

Return a set of tuples of the form `(index, element)`

.

The `enumerate()`

function takes any set and produces a set of
tuples containing the zero-based index number and the value for each
element.

The ordering of the returned set is not guaranteed, however the assigned indexes are guaranteed to be in order of the original set.

db>

`SELECT enumerate({2, 3, 5});`

{(1, 3), (0, 2), (2, 5)}

db>

`SELECT enumerate(User.name);`

{(0, 'Alice'), (1, 'Bob'), (2, 'Dave')}

Return the smallest value of the input set.

db>

`SELECT min({-1, 100});`

{-1}

Return the greatest value of the input set.

db>

`SELECT max({-1, 100});`

{100}