Aggregation with count, collect, sum, avg, min/max, Grouping Keys, and Cardinality Reasoning

Learning outcomes

AtlasMart's API team wants one customer summary row containing order count, gross spend, average order value, range of order totals, and a list of order identifiers. The risk is not the function names; it is misunderstanding which incoming rows are grouped together and silently returning a different granularity.

Re-establish the deterministic AtlasMart read fixture

Every lesson can stand alone. The fixture is idempotent, so rerunning it is the safest reset: it restores four customers, five orders, four products, two categories, five PLACED, seven CONTAINS, and four IN_CATEGORY relationships without deleting the Chapter 04 operations graph. If your course sandbox already contains the fixture, this simply normalizes the properties used here.

CYPHER 25CREATE CONSTRAINT customer_id IF NOT EXISTS FOR (c:Customer) REQUIRE c.customerId IS UNIQUE;CREATE CONSTRAINT product_id IF NOT EXISTS FOR (p:Product) REQUIRE p.productId IS UNIQUE;CREATE CONSTRAINT order_id IF NOT EXISTS FOR (o:Order) REQUIRE o.orderId IS UNIQUE;CREATE CONSTRAINT category_id IF NOT EXISTS FOR (c:Category) REQUIRE c.categoryId IS UNIQUE;MERGE (c1:Customer {customerId:'C-1001'}) SET c1.name='Ava Chen', c1.tier='gold', c1.region='eu'MERGE (c2:Customer {customerId:'C-1002'}) SET c2.name='Noah Smith', c2.tier='silver', c2.region='us'MERGE (c3:Customer {customerId:'C-1003'}) SET c3.name='Mina Rahimi', c3.tier='gold', c3.region='me'MERGE (c4:Customer {customerId:'C-1004'}) SET c4.name='Leo Martin', c4.region='eu'MERGE (cat1:Category {categoryId:'CAT-CAMERAS'}) SET cat1.name='Cameras'MERGE (cat2:Category {categoryId:'CAT-AUDIO'}) SET cat2.name='Audio'MERGE (p1:Product {productId:'P-1001'}) SET p1.name='Trail Camera', p1.price=129.90, p1.rating=4.7MERGE (p2:Product {productId:'P-1002'}) SET p2.name='Studio Headphones', p2.price=89.00, p2.rating=4.7MERGE (p3:Product {productId:'P-1003'}) SET p3.name='Action Camera', p3.price=219.00, p3.rating=4.5MERGE (p4:Product {productId:'P-1004'}) SET p4.name='USB Microphone', p4.price=75.00MERGE (p1)-[:IN_CATEGORY]->(cat1)MERGE (p3)-[:IN_CATEGORY]->(cat1)MERGE (p2)-[:IN_CATEGORY]->(cat2)MERGE (p4)-[:IN_CATEGORY]->(cat2)MERGE (o1:Order {orderId:'O-2001'}) SET o1.placedAt=datetime('2026-08-01T09:00:00Z'), o1.status='paid', o1.total=218.90MERGE (o2:Order {orderId:'O-2002'}) SET o2.placedAt=datetime('2026-08-02T10:30:00Z'), o2.status='paid', o2.total=219.00MERGE (o3:Order {orderId:'O-2003'}) SET o3.placedAt=datetime('2026-08-03T12:00:00Z'), o3.status='shipped', o3.total=129.90MERGE (o4:Order {orderId:'O-2004'}) SET o4.placedAt=datetime('2026-08-04T14:15:00Z'), o4.status='paid', o4.total=164.00MERGE (o5:Order {orderId:'O-2005'}) SET o5.placedAt=datetime('2026-08-04T14:15:00Z'), o5.status='paid', o5.total=75.00MERGE (c1)-[:PLACED]->(o1)MERGE (c2)-[:PLACED]->(o2)MERGE (c1)-[:PLACED]->(o3)MERGE (c3)-[:PLACED]->(o4)MERGE (c4)-[:PLACED]->(o5)MERGE (o1)-[:CONTAINS {quantity:1}]->(p1)MERGE (o1)-[:CONTAINS {quantity:1}]->(p2)MERGE (o2)-[:CONTAINS {quantity:1}]->(p3)MERGE (o3)-[:CONTAINS {quantity:1}]->(p1)MERGE (o4)-[:CONTAINS {quantity:1}]->(p2)MERGE (o4)-[:CONTAINS {quantity:1}]->(p4)MERGE (o5)-[:CONTAINS {quantity:1}]->(p4);

Before analytical work, verify the relevant slice rather than assuming the whole database contains only these entities:

CYPHER 25MATCH (c:Customer) WITH count(c) AS customersMATCH (o:Order) WITH customers, count(o) AS ordersMATCH (p:Product) WITH customers, orders, count(p) AS productsMATCH (:Customer)-[placed:PLACED]->(:Order)WITH customers, orders, products, count(placed) AS placedMATCH (:Order)-[line:CONTAINS]->(:Product)RETURN customers, orders, products, placed, count(line) AS contains;

For the fixture above the invariant is 4 / 5 / 4 / 5 / 7. If Chapter 04 remains loaded, the total database node/relationship counts will be larger; label- and type-specific counts are the contract for this chapter.

1. Aggregation is a row-granularity decision

Aggregation consumes a stream of rows and combines values across rows. A grouping key identifies which rows belong to the same bucket. In classic Cypher syntax grouping keys are implicit: every non-aggregating projection in the same RETURN or WITH becomes a grouping key. That means adding a seemingly harmless projected column can change the number of result rows.

CYPHER 25MATCH (c:Customer)-[:PLACED]->(o:Order)RETURN c.customerId AS customerId,       count(*) AS orderRows,       count(o.total) AS totalsPresent,       round(sum(o.total), 2) AS gross,       round(avg(o.total), 2) AS avgOrder,       min(o.total) AS minOrder,       max(o.total) AS maxOrderORDER BY customerId;

For this deterministic fixture, Ava has two incoming order rows and the other customers have one each. The aggregation boundary collapses those five order rows into four customer rows. The important mechanism is the row transition, not the numeric result.

2. count and collect have null-sensitive semantics

count(*) counts incoming rows, even when projected values on a row are null. count(expression) counts only non-null values of that expression. collect(expression) also ignores null values; collect(null) yields an empty list. This matters because AtlasMart deliberately has one customer without a tier property.

CYPHER 25MATCH (c:Customer)RETURN count(*) AS customerRows,       count(c.tier) AS customersWithTier,       collect(c.tier) AS observedTiers;

The fixture deterministically gives four customer rows and three non-null tiers. Do not turn the order of observedTiers into an API contract unless the query establishes ordering before collection.

3. Explicit GROUP BY in Cypher 25 vs implicit grouping

Neo4j 2026.07 added GQL-conformant GROUP BY to Cypher 25. It makes the grouping decision visible, which is valuable in review-heavy analytical code. Cypher 5 remains frozen and does not gain this newer syntax, so compatibility-sensitive lessons still need to understand implicit grouping.

CYPHER 25MATCH (c:Customer)-[:PLACED]->(o:Order)RETURN c.region AS region,       count(o) AS orderCount,       round(sum(o.total), 2) AS grossGROUP BY regionORDER BY region;

The compatible implicit form is the same projection without the GROUP BY region line. Do not claim the two forms are interchangeable on an older server merely because the business meaning is the same; syntax availability is version-dependent.

4. Deliberately wrong: an accidental extra grouping key

Suppose AtlasMart wants one row per region but a developer includes customerId for debugging and forgets to remove it:

CYPHER 25MATCH (c:Customer)-[:PLACED]->(o:Order)RETURN c.region AS region,       c.customerId AS customerId,       count(o) AS orderCount,       sum(o.total) AS grossORDER BY region, customerId;

The result is not “region totals with an annotation”; it is a different grouping contract: region + customer. The repair is to remove customerId from the aggregation boundary, or aggregate customer IDs deliberately with collect(DISTINCT c.customerId) if the API actually needs them.

5. A final aggregate can hide a Cartesian product

A disconnected pattern multiplies independent row sets. Four customers times four products creates sixteen intermediate combinations. A final aggregate can compress those sixteen rows back to one row, hiding the expensive mistake.

CYPHER 25EXPLAINMATCH (c:Customer), (p:Product)RETURN count(*) AS combinations,       count(DISTINCT c) AS customers,       count(DISTINCT p) AS products;

On this fixture the logical combinations are sixteen even though the final result is one row. Inspect the plan and row flow; operator names and estimates are release-sensitive. The repair is to express the relationship that justifies pairing the entities, or compute independent aggregates in separate scoped subqueries rather than multiplying them.

CYPHER 25CALL () { MATCH (c:Customer) RETURN count(c) AS customers }CALL () { MATCH (p:Product) RETURN count(p) AS products }RETURN customers, products;

Production judgment

Aggregation correctness is an API contract. Review the grain of every stage, not only the final column names. Large collect() values can retain substantial data in query memory and produce oversized driver payloads; an index can improve how rows are found but cannot eliminate the memory cost of collecting an unbounded group. Measure cardinality and memory on representative degree distributions, and prefer bounded summaries, pagination, or downstream analytical systems when a request wants effectively unbounded history.

Summary and next step

Aggregation is now a visible change of grain: know the incoming rows, define the grouping keys, understand null behavior, and inspect intermediate work before trusting the final summary. Next, keep one row but reshape values inside it with list expressions, comprehensions, predicates and reduce().

Authoritative references

• Current Neo4j versions — Release/LTS snapshot used to pin the course baseline.
• Cypher Manual — Introduction — Cypher 25 status and language-version policy.
• Select Cypher version — Database/default and per-query Cypher version behavior.
• Aggregating functions — count, collect, sum, avg, min/max and grouping-key semantics.
• GROUP BY — Cypher 25 explicit grouping introduced in Neo4j 2026.07.
• ORDER BY — Ordering contract for deterministic results.