Aggregations

Aggregation functions sit inside :find and collapse multiple rows into a single value. Any non-aggregated variable in :find becomes a grouping key — like SQL's GROUP BY.

count

How many Pokemon belong to each type? ?type is the grouping key; (count ?name) counts entries per group:

[:find ?type (count ?name) :where [?e :pokemon/name ?name] [?e :pokemon/type ?type]]

min and max

What is the highest and lowest speed in the Pokedex?

[:find (min ?speed) (max ?speed) :where [?e :stat/speed ?speed]]

Add ?name as a grouping key to see each Pokemon's speed alongside the extremes within its group — or move on to the Find Specifications chapter where you'll learn how to return a single value as a scalar.

avg and sum

Average attack across all Pokemon:

[:find (avg ?attack) :where [?e :stat/attack ?attack]]

Sum of all HP values — a rough measure of total bulk across the Pokedex:

[:find (sum ?hp) :where [?e :stat/hp ?hp]]

The :with clause

There is a subtle trap with cardinality/many attributes. Before aggregation, Datomic builds a set from the :find variables. Rows that look identical after projecting onto those variables are merged — which can silently drop data you intended to aggregate.

Compare these two queries. The first counts distinct type strings in the database:

[:find (count ?type) :where [?e :pokemon/type ?type]]

The second uses :with to include ?e in the pre-aggregation set, preventing type strings from collapsing across entities. It counts total type assignments across all Pokemon:

[:find (count ?type) :with ?e :where [?e :pokemon/type ?type]]

The first result is the number of unique type names (around 15). The second is the total number of type slots filled across all ~150 Pokemon (higher, because dual-typed Pokemon contribute two entries each).

count-distinct

count-distinct counts unique values of a variable within each group, ignoring duplicates. Its difference from count becomes visible when :with introduces duplicate rows. These two queries group by type and measure speed values in each group. With :with ?e, the same speed can appear multiple times (once per Pokemon with that type). count tallies every speed slot; count-distinct collapses repeated speeds:

[:find ?type (count ?speed) :with ?e :where [?e :pokemon/type ?type] [?e :stat/speed ?speed]]

Now the same query using count-distinct — for types where multiple Pokemon share a speed value, the count will be lower:

[:find ?type (count-distinct ?speed) :with ?e :where [?e :pokemon/type ?type] [?e :stat/speed ?speed]]

Try: Add ?type as a grouping key to the avg query to see average attack broken down by type. Or use (min ?attack) and (max ?attack) together to see the attack range per type.

Previous | Next