Aggregation & Grouping

Pattern matching is excellent at expanding connectivity: one actor who starred in four movies can produce four rows; co-actor queries can explode further when two people share several films. Business questions rarely want every intermediate path. They want summaries—“how many movies did Tom Hanks act in?”, “what is the average review score for The Matrix?”, “list every director with the titles they directed.” Aggregation is the tool that turns a bag of matched paths into counts, totals, extremes, and collected lists.

In Cypher, aggregation is expression-driven rather than clause-driven like SQL’s explicit GROUP BY. When a WITH or RETURN contains aggregating functions, every non-aggregating expression in that clause becomes an implicit grouping key. Rows that share the same key values form one group; each aggregating function reduces the group to a single value. Understanding that rule is more important than memorizing function names.

All worked examples in this course assume the classic Neo4j Movie graph: nodes labeled Person and Movie, with relationships such as ACTED_IN, DIRECTED, PRODUCED, WROTE, and REVIEWED. Property names like name, title, released, and rating match the public Movie dataset conventions so you can paste queries into Neo4j Browser or Aura after loading the sample.

count(*) counts rows in the group. Use it when every matched path should contribute, including rows that carry null properties. count(expression) counts only rows where the expression is non-null—critical after OPTIONAL MATCH or when a property may be missing. Prefer count(DISTINCT x) when path multiplicity would otherwise count the same entity twice.

collect(expression) builds a list of non-null values from the group. Empty groups yield an empty list, not null. Lists are first-class values: you can return them, pass them through WITH, later UNWIND them (Lesson 07), or filter them with list comprehensions (Lesson 09).

Numeric reducers—sum, avg, min, and max—ignore null inputs. avg over zero non-null values yields null. Mixing types that cannot compare or sum will fail at runtime; keep review scores numeric and release years integers when you plan to aggregate them.

The query above never writes GROUP BY p.name. Cypher infers the group because p.name is not wrapped in an aggregating function while the other returned expressions are. If you accidentally return both p.name and m.title without aggregating the title, you are not grouping by actor alone—you are returning one row per actor–movie pair.

Real analytical pipelines almost never finish in a single clause. You filter people, expand movies, count them, then filter groups with high counts, then expand again. Each time you introduce aggregation, you must decide which bindings survive. That decision lives on WITH.

Think of WITH as a pipeline valve: only the expressions you project remain available downstream, and aggregation on that valve collapses rows before the next MATCH. Placing aggregation too late lets intermediate cartesian-style growth inflate counts. Placing it too early drops variables you still need—unless you collect them into lists first.

Filtering after aggregation uses WHERE on a subsequent clause (or a WITH … WHERE form). That is Cypher’s analogue of SQL HAVING: first form groups, then keep groups that satisfy a predicate on the aggregate.

Graphs create legitimate duplicate logical entities across different paths. If you match (a:Person)-[:ACTED_IN]->(m)<-[:ACTED_IN]-(b:Person) to find co-actors, Alice and Bob may share three movies and appear three times. A plain count(b) then answers “how many co-acting path rows exist,” not “how many unique co-actors exist.” Use count(DISTINCT b) or project distinct pairs before counting.

RETURN DISTINCT deduplicates entire result rows after projection. It is not a substitute for understanding multiplicity inside aggregation, but it is useful when you only need unique projected maps or values. Prefer precise DISTINCT inside aggregations when the goal is a correct count rather than a cosmetic unique list.

Version note: aggregation semantics for null skipping and list collection have been stable for years, but planner choices and available aggregating functions can grow across Neo4j releases. Always check the aggregating functions page of the Cypher Manual for your server major version when adopting newer helpers.