They are both ultimately implemented the same way: Repo.aggregate ultimately calls Ecto.Repo.Queryable.query_for_aggregate:

This first takes care of a detail: using a LIMIT on a COUNT requires a subquery.

Then it builds an AST equivalent to writing select: count() and replaces any previous select value on the query.

There’s also a head for query_for_aggregate that produces the AST equivalent of select: count(some_field).

Both options exist because they can do different things:

• Repo.aggregate knows how to deal with limit etc. Building with select: count requires handling that manually
• select: count supports (where available) the filter syntax
• select: count can be used as part of a larger structure, for instance:

from(o in Organization,
  left_join: w in Workspace,
  on: w.org_id = o.id
  select: {o.id, count(w.id)},
  group_by: o.id
) |> Repo.all()

which returns a list of {id, count} tuples. Repo.aggregate does not support group_by at all.