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:

min and max

What is the highest and lowest speed in the Pokedex?

Add ?name as a grouping key to see each Pokemon's speed alongside the extremes within its group.

avg and sum

avg and sum look simple, but both queries below give the wrong answer. The next section explains why. Run them anyway, and see whether you can spot the problem first.

Average attack across all Pokemon. Note the number you get:

Now add up the HP of every Pokemon, a rough measure of total bulk across the Pokedex. Before you run it, think about how many Pokemon there are and how much HP a typical one has, then guess the total:

The total is 3070 for 151 Pokemon. That works out to about 20 HP each, far less than a typical Pokemon has. Something is off.

The :with clause

The sum is too small because of how Datomic aggregates. Before aggregation, Datomic builds a set from the :find variables (plus :with variables). Rows that look identical after projecting onto those variables are merged. The HP query has only one variable, ?hp, so two Pokemon with the same HP become a single row, and the second one is never added.

The :with clause fixes this. A variable in :with keeps rows apart before aggregation, but is not returned. Adding :with ?e keeps one row per Pokemon:

The sum is now 9696. The average attack has the same problem, so here it is again with the fix:

Compare it with the number you noted earlier: the average changed too. A sum that is far too small is easy to notice. A wrong average is harder to catch: off by a couple of points, it still looks plausible.

The same thing happens with count. Compare these two queries. The first counts distinct type strings in the database:

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:

The first result, 17, is the number of unique type names. The second, 218, is the total number of type slots filled across all 151 Pokemon. It is higher because dual-typed Pokemon contribute two entries each.

When can you skip :with? When rows cannot collapse, or when collapsing does not matter. (count ?name) in the count section was safe because Pokemon names are unique, so no two rows merge. min and max are immune because a duplicate never changes an extreme. For sum, avg and count over a value that different Pokemon can share, like HP, attack or type, add :with ?e.

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:

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